Counting the number of days from a given date to today is a very common thing in life.
When we need to know the age of a newborn baby, the number of days till a deadline, the number of expired days of a contract, financial calculations based on the number of days, and so on, we use this method.
In this article, I will show you some simple formulas that you can use to count days from date to today.
Formula 1: Using Simple Subtraction to Count Days from Date to Today
When you want to get the number of days between a date and today, you can simply subtract the given date from today’s date.
Below I have a table that shows a start date of a task in cell A2. Now, I want to count the number of days from that date to today.
I can use the below formula to do the calculation.
=TODAY()-A2
To get today’s date, I have used the Excel TODAY function. The syntax of the TODAY function is TODAY(). You don’t need any arguments for the TODAY function. As soon as you enter TODAY(), you will get today’s date.
Then, I subtracted the date from today’s date. Today is 9th June 2023. So, Excel calculates the number of days between 06/09/2023 and 02/24/2023.
Do you know how Excel calculates the count between two dates? Excel always stores dates as sequential serial numbers. Serial number 1 is January 1, 1900, serial number 2 is January 2, 1900, and so on. So, February 24, 2023, is stored as 44981 because it is 44980 days after January 1, 1900. Then, using these serial numbers of dates, Excel does the calculations.
When you do the subtraction, Excel will return the result in date format, like the image below.
You have to then change the cell formatting to General formatting to see the result as a count. To change the cell formatting, you can follow the below steps.
- Select the cell that contains the formula (in this case it is cell B2) and go to the Home tab.
- Go to the “Number” group and expand the “Number Format” drop-down.
- Select “General” from the drop-down list.
As soon as you select “General”, Excel shows the result of the formula as a number. In this case, Excel shows 105 instead of 04/14/1900.
Let’s say that you want to find the number of days between today and a future date.
Then, when you apply the above formula, you will get the count as a negative number.
To avoid the negative sign, you can do one of the modifications to the above formula.
- In the previous formula, I subtracted the given date from the TODAY function. Now, when you have a future date, subtract the TODAY function from the future date.
=TODAY()-A2
- Use the Excel ABS function and get the absolute value.
=ABS(A2-TODAY())
It is important to note that you have to enter dates in the Excel sheet in a date format. For example, if you have entered dates as text, Excel will show you an error. So, before you do the subtraction, check whether the dates are in the proper date format.
Also read: How to Get Total Days in Month in Excel?
Formula 2: Using the DAYS Function to Count Days from Date to Today
Excel is loaded with many useful functions. When you want to find the number of days between today’s date and another date, you can easily use the Excel DAYS function.
Below I have a table that shows a start date of a task in cell A2. Now, I want to count the number of days from that date to today.
I can use the below formula to do the calculation.
=DAYS(TODAY(),A2)
The syntax of the DAYS function is DAYS(end_date, start_date). In this function, first I have to enter the end date.
The end day would be today’s date which I get using the TODAY function. The syntax of the TODAY function is TODAY(). I don’t need any arguments for the TODAY function. As soon as I enter TODAY(), I get today’s date.
The second argument of the DAYS function is the start date. I have selected cell A2 for that argument.
If I want to calculate the number of days between a future date, I have to enter today’s date as the start date and the other given date as the end date. If not, Excel returns the number of days between two dates as a negative number.
Compared to the simple subtraction method in Formula 1, this function directly gives the result as a number. So, I don’t need to go and change the formatting of the result cell.
Also read: Calculate Days Left in the Year (Formula)
Formula 3: Using the DATEDIF Function to Count Days from Date to Today
Excel has some hidden functions. The DATEDIF function is such a hidden function that I can use it to calculate the number of days between today’s date and another date.
I am calling this function a hidden function because you can’t find this function from the function list. But, still, we can use this function for calculations.
Below I have a table that shows a start date of a task in cell A2. Now, I want to count the number of days from that date to today.
I can use the below formula to do the calculation.
=DATEDIF(A2,TODAY(),"d")
The syntax of the DATEDIF function is DATEDIF(start_date, end_date, unit).
It is important to know that this function is undocumented in Excel, such as EVALUATE, FILES, and GET.CELL.
Unlike other functions, undocumented functions cannot be found in the function list and will not appear when you start typing the name of the function.
However, these functions still work in Excel (you just need to know the right syntax).
The first argument of the DATEDIF function is the start date. So, we can select cell A2 for that.
The next argument is the end date. For that, I have to enter today’s date. To get today’s date, I have used the TODAY function.
The final argument of this function is “unit”. As I need the number of days between the selected days, I have to enter the letter D within double quotes. The letter D represents the days.
Also read: Calculate the Number of Months Between Two Dates in Excel
Formula 4: Using the NETWORKDAYS Function to Count Work Days from Date to Today
When we are working in an office environment, sometimes we need to find the number of work days from a given date to today’s date.
For example, if I am doing an assigned task, I have to calculate network days to find how many days I have spent on that task until today.
Below I have a table that shows a start date of a task in cell A2. Now, I want to count the number of workdays (excluding Saturday and Sunday) from that date to today.
I can use the below formula to do the calculation.
=NETWORKDAYS(A2,TODAY())
When you want to find the number of days between two days while excluding Saturday and Sunday, the NETWORKDAYS function is the best function that you can use.
I can use this function for this case, as I want to exclude weekend dates from the count. The syntax of the NETWORKDAYS function is NETWORKDAYS(start_date, end_date, [holidays]).
As the first argument, I have selected the given date. So, I have selected cell A2.
Then, for the second argument, I have to enter today’s date. To enter today’s date, I have used the TODAY function.
The syntax of the TODAY function is TODAY(). You don’t need any arguments for the TODAY function. As soon as you enter TODAY(), you will get today’s date.
The third argument is holidays, and it is an optional argument. In this case, I have ignored this last argument. If you want you can select the holiday dates as a range for the last argument.
Suppose you get a #VALUE! error value for the result of this function, it means you have entered an invalid date for an argument. Microsoft recommends entering dates for the function, using the DATE function, or as a result of other formulas or functions.
Also read: How to Add Days to a Date in Excel
Formula 5: Using the NETWORKDAYS.INTL Function to Count Work Days from Date to Today
In the above function, non-working dates are fixed for Saturday and Sunday.
But, in some companies and some countries, non-working days are not Saturday and Sunday.
It can be varied in different ways. For example, you may be working in an office where only Sunday is the off day, or Friday and Saturday are the weekend days.
In such a situation, we cannot use the Excel NETWORKDAYS function to count workdays between two dates.
Then, we have to use Excel NETWORKDAYS.INTL function. This function has the option to select which dates should be considered as weekend dates.
Below I have a table that shows a start date of a task in cell A2. Now, I want to count the number of workdays (excluding Sundays only) from that date to today.
I can use the below formula to do the calculation.
=NETWORKDAYS.INTL(A2,TODAY(),11)
The syntax of the NETWORKDAYS.INTL is NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]).
The first argument of the function is the start date. For the start date, I have selected the given date in cell A2.
Then, I have to enter the end date. In this case, the end date is today’s date. So, I used the Excel TODAY function. The syntax of the TODAY function is TODAY(). I don’t have to enter any arguments for the TODAY function.
The third and the fourth arguments of the NETWORKDAYS.INTL function is optional. The third argument is the weekend. I can select one of the given options as weekend dates.
In this case, I want to consider only Sunday as the weekend. In the above list, option 11 is Sunday only.
So, I have entered 11 for the third argument. If I have not selected an option for the third argument, Excel considers option 1 – Saturday and Sunday as weekends.
So, this means if I have not entered the third option, the result of the NETWORKDAYS.INTL is exactly matched to the result of the NETWORKDAYS function.
I can also enter this third argument as a weekend string value.
The starting string value is Monday, and I can enter 1 to represent non-workday and 0 to represent workday.
I have to enter the weekend string value inside double quotes. In this case, I can use “0000001” for the third argument to represent that Sunday is the only non-workday.
If I want to exclude holidays from the count, I can set holiday dates as a range to the last argument of the function.
You can select the most suitable option to count days from a given date to today. To minimize errors, always remember to enter dates in a valid format.
Other Excel articles you may also like:
