Excel Formula to Count Days from Date to Today (8 Effective Ways)

Counting the number of days in Excel is a significant task in our day-to-day activities. But what are the Excel formulas to do that in an efficient way?

In this article, I’m going to discuss eight effective Excel formulas, and even utilize Power Query, to count the number of days.

Download Practice Workbook

Excel Formula to Count Days from Date to Today

Before going into the main discussion, let’s look at our data set.

In the following figure, employee names with their joining date are given. We have to count the number of days from the joining date to Today.

Data set y to Count Days from Date to Today

Let’s get started

1. Using Subtraction Formula

We can count the number of days from the joining date till today effortlessly using the subtraction formula.

The subtraction formula, the easiest one to deduct, might be easily applied by entering the minus (-) operator between the two dates.

For this, select a blank cell e.g. E5.

Next, type the formula =D5-C5 where D5 is the date of the current day and C5 is the date of joining date.

Finally, press Enter.

Subtraction Formula to Count Days from Date to Today

➥ Read More: How to Subtract/Minus Days from Today’s Date in Excel (4 Simple Ways)

2. Employing TODAY Function

If you don’t want to create a different cell of the current date, you can always utilize the TODAY function.

This function returns the current date, which is updated constantly if the data set is modified or opened. No arguments are presented in the TODAY function. The syntax of the function is

=TODAY ()

To apply the function in our data set, select a blank cell e.g. D5, and type the formula like =TODAY()-C5 to calculate the total number of days since an employee has joined the office. Here C5 is the joining date of the employee.

And then, press Enter.

TODAY Function to Count Days from Date to Today

3. Using DAYS Function

Another simple way to count the number of days between two dates, in this case, the joining date and the current date, is to apply the DAYS function.

The function yields the number of days between two Excel dates. The syntax of the function is

=DAYS (end_date, start_date) 

The arguments of the function are the following

end_date – The end date.

start_date – The start date.

Let’s apply the function in our data set. Now, select a blank cell like D5.

Insert the formula =DAYS(D5,C5) where D5 is the date of the current day and C5 is the date of joining date.

Finally, press Enter.

DAYS Function to Count Days from Date to Today

Here’s an important thing, you can also use TODAY() in the place of end-date to count the days.

And you’ll get the same results. In that case, the formula will be

=DAYS(TODAY(),C5)

DAYS & TODAY Function to Count Days

4. Applying DATE Function

You can calculate the number of days using the DATE function.

The DATE function returns the serial number that a particular date holds.

The syntax of the function is

=DATE (year, month, day) 

The arguments of the function are the following

year – Number for the year.

month – Number for the month.

day – Number for the day.

As we have one cell that contains the date, we can use the YEAR, MONTH, DAY function to extract the year, month, and date respectively. Also, you can input the data manually.

The YEAR function extracts the year from a given date. The syntax of the function is

=YEAR (date) 

The Excel MONTH function extracts the month from a given date

=MONTH (serial_number) 

The Excel DAY function returns the day of the month as a number between 1 to 31 from a given date.

=DAY (date) 

Therefore, our formula takes the form:

=DATE(YEAR(D5),MONTH(D5),DAY(D5))-DATE(YEAR(C5),MONTH(C5),DAY(C5))

Where D5 is the date of the current day and C5 is the joining date

Now, select a blank cell, input the formula, and press Enter.

DATE Function to Count Days from Date to Today

5. Employing DATEDIF Function

The use of the DATEDIF function is also a way to count days between Excel dates. It is specially intended to determine the time difference in different units, including days, months, and years.

The syntax of the function is

=DATEDIF (start_date, end_date, unit) 

The arguments of the function are the following

start_date – Start date in Excel date serial number format.

end_date – End date in Excel date serial number format.

unit – The time unit to use (years, months, or days).

So the formula for our data set is =DATEDIF(C5,D5,”d”) where D5 is the date of the current day and C5 is the date of joining date. Also, d refers to days (full days).

Now enter the formula in a blank cell, press Enter.

Likewise the DAYS function, you can also use the TODAY function here instead of end_date.

DATEDIF Function to Count Days from Date to Today

6. Applying NETWORKDAYS Function

If you want to count working days and exclude the weekend days and holidays, you can use the NETWORKDAYS function.

Excel NETWORKDAYS function counts the number of working days between two dates. Saturday and Sunday are automatically excluded in the function and optionally a list of the holidays might be excluded.

It means that you can’t change the default weekend days in the function.

The syntax of the function is

=NETWORKDAYS (start_date, end_date, [holidays]) 

The arguments of the function are the following

start_date – The start date.

end_date – The end date.

holidays – [optional] A list of non-work days as dates.

Firstly, let’s count the number of working days excluding the default weekend days, and not counting the holidays.

So, select a blank cell and insert the formula =NETWORKDAYS(C5,D5) where C5 is the joining date and D5 is the date of the current day.

NETWORKDAYS to Count Days from Date to Today

Now, we can include a list of the holidays. It is not mandatory that the cell range of the holidays is close to the other two dates. But make sure that the Dollar ($) sign is available in the cell range so that it will work for all employees.

Therefore, the formula will be:

=NETWORKDAYS(C5,D5,$E$5:$E$13) 

where C5 is the joining date, D5 is the date of the current day (you can use the TODAY function instead of it) and $E$5:$E$13 is the cell range for holidays. After that, select a blank cell and press Enter.

You’ll get the following output.

NETWORKDAYS to Count Days from Date to Today

7. Using NETWORKDAYS.INT Function

As you know, weekend days vary from country to country. For example, Friday and Saturday are weekend days in some countries.

One main limitation of the NETWORKDAYS function is fixed on Saturday and Sunday. If you want to customize the weekend days, then NETWORKDAYS.INT function will be the right option for you.

This function also counts the number of working days excluding the customized weekend days and holidays.

The syntax of the function is-

=NETWORKDAYS.INTL (start_date, end_date, [weekend], [holidays]) 

The arguments of the function are the following

start_date – The start date.

end_date – The end date.

weekend – [optional] Setting for which days of the week should be considered weekends.

holidays – [optional] A reference to dates that should be considered non-work days.

To apply the formula in our data set, select a blank cell e.g. E5. Then type the formula =NETWORKDAYS.INTL(C5,D5,7) where C5 is the joining date and D5 is the date of the current day and 7 is for the weekend days of Friday and Saturday.

You’ll see the following options while inputting the formula.

weekend NETWORKDAYS to Count Days from Date to Today

After pressing Enter, you’ll get the following result.

NETWORKDAYS.INTL to Count Days from Date to Today

Later, if you can include holidays in the function. In that case, the formula will be

=NETWORKDAYS.INTL(C5, D5,7,$E$5:$E$13) where C5 is the joining date and D5 is the date of the current day, 7 is for the weekend days of Friday and Saturday and $E$5:$E$13 is for holidays.

NETWORKDAYS.INTL to Count Days from Date to Today

 

8. Utilizing Power Query

If you want to do the same analysis using Power Query that I have done using different Excel functions, it will be a good decision.

Power Query, one of the most powerful business intelligence tools, requires the user not to learn a particular code.

The first feature made available in MS Excel in 2010, Excel 2010 and 2013, is a free add-in. It has been fully integrated into Excel since 2016.

Here, I am showing only the subtraction process using Power Query.

Steps:

  • Select the cell range B4:D13>From Table(available in Data tab)>Create Table>press Ok

Power Query to Count Days from Date to Today

  • Add Column>press CTRL key>select the two columns i.e. Joining Date and Today>click on Date>Subtract Days
  • Instead of doing this, you can use the formula bar. The formula will be
=Table.AddColumn(#"Changed Type", "Subtraction", each Duration.Days([Joining Date] - [Today]), Int64.Type) 

Power Query to Count Days from Date to Today

  • Select Home tab>Close & Load>Close & Load To

Power Query to Count Days from Date to Today

  • Select Existing worksheet>press Load

Power Query to Count Days from Date to Today

Finally, you’ll get the output like the following

Output Using Power Query to Count Days from Date to Today

Things to Keep in Mind

While working dates related tasks in Excel, you must be aware that Excel works mainly based on serial numbers.

Excel stores serial numbers for doing calculations with dates.

If your output or even cell containing dates get changed into a serial number, don’t panic. Just change the format from Format Cells like the following

format cells

Also be careful about the file name, file location, and Excel extension name.

Conclusion

Now, you’ve got these formulas and methods to count days from a date to today. If you have any opinions, please share them below in the comments section.

Thanks for being with me.


Further Readings

2 Comments
  1. How do you total a group of dates to get a final total? This looks like 19 to 20 years total, but is there a formula that can total this up?

    2 years, 8 months, 19days
    1 years, 1 months, 0days
    13 years, 8 months, 0days
    3 years, 4 months, 0days

Leave a reply

ExcelDemy
Logo