Excel Formula to Count Days from Date (5 Easy Methods)

When working with dates in Excel, we often need to count the number of days from the date using a formula. In the past, people used to calculate it manually. But at present with the advancement of modern tools, it is quite easy to calculate it using these modern tools.

Today I will be showing how to use an Excel formula to count the number of days from date using Microsoft 365 version.


Download Practice Workbook

You can download the practice workbook from here:


How to Add Dates in Excel

Excel has built-in formats for writing any date inside it. If you do not know it, just click any cell and write any date inside it, in our conventional way, DD/MM/YYYY. As I have written, 09-03-11.

Inserting Date for using Excel Formula to Count Days from Date

Now select it and go to the Home >> Number section in the Excel Toolbar. You will see the Date option is automatically selected thereby Excel.

If you want to change the format, select the dropdown menu with it. You will get quite a few options like General, Number, Currency, Percentage, etc.

  • Now, select the last option More Number Formats.

At this time, you will get a dialogue box named Format Cells.

  • Now, you see under the Type menu, there are various sorts of Date Format. Click on the one you like. Here, I am choosing the one, on March 14, 2012.
  • Then, click OK.

In this way, you can write the Date in any desired format in Excel.

➥ Related: How to Add Dates in Excel Automatically


5 Formulas to Count Days from Date in Excel

Let us have a data set like this. Here, we have the employee record of a company named Tata Group. Furthermore, we have the Employee Names, Their Starting Dates, and Ending Dates in columns B, C, and D respectively.

Dataset for using Excel Formula to Count Days from Date

Here, the CEO of the company wants to find out the number of total days each employee worked. How can he find this out? Now, we are showing the ways.


1. Employing Normal Subtraction to Count Days from Date in Excel

Here, you may use the general subtraction formula as an Excel formula to count the number of days from date. Additionally, we are going to use the following formula.

= Ending Date – Starting Date

Now, let’s talk about the steps.

  • First of all, select a column where you want to have the total days.  Here, we have selected column E and named it Total Days.
  • Then, select the first cell of it. Here, we have selected the first cell of it, E5.
  • After that, write the corresponding formula in the E5 cell.
=D5-C5

Use of Normal Subtraction Excel Formula to Count Days from Date

  • Subsequently, press ENTER.

See Excel has calculated the total number of days between the two days, 3179.

  • Now, to find out the total days of all the employees, drag the Fill Handle icon (Small Plus (+) Sign on the bottom right corner) or double-click it.

As a result, you will find all the cells filled with the formula and the number of days.


2. Applying DAYS Function to Count Days from Date in Excel

Excel provides a built-in function called the DAYS function. It takes two arguments, the Ending Date and the Starting Date. And gives the total number of days in between as the output. Now, let’s talk about the steps.

  • Firstly, select the first cell of the column where you want to have the total days. Here, we are again selecting the E5 cell.
  • Secondly, write down the formula.
=DAYS(D5,C5)
  • Lastly, click ENTER.

Use of DAYS Function as an Excel Formula to Count Days from Date

See we have got the number of days, 3179.

  • Now, like the previous one, drag the Fill Handle icon and fill all the cells of the column with the same formula.

Result for using Excel Formula to Count Days from Date

Note: DAYS function is available from Excel 2013. So the users of previous versions will not find this.

3. Counting Days by Excel’s DATEDIF Function

Here, you may use the DATEDIF function as an Excel formula to count the number of days from date. Additionally, the structure of this function is as below.

=DATEDIF (Starting Date, Ending Date, “d”)

It calculates the number of days between two dates, just like the DAYS Function. Only one difference, it takes Starting Date as the first argument, while DAYS takes Ending Date first.

Now, let’s talk about the steps.

  • First of all, select a column where you want to have the total days.  Here, we have selected column E and named it Total Days.
  • Then, select the first cell of it. Here, we have selected the first cell of it, E5.
  • After that, write the corresponding formula in the E5 cell.
=DATEDIF(C5,D5,"d")
  • Subsequently, press ENTER.

See we have got the number of days, 3179.

Use of DATEDIF Function as an Excel Formula to Count Days from Date

  • Now, like the previous one, drag the Fill Handle icon and fill all the cells of the column with the same formula.

Lastly, we got the number of days between the two dates.

Here, another structure of this function is as below.

=DATEDIF (Starting Date, Ending Date, “m”)

It calculates the number of months between two days.

Another format is:

=DATEDIF (Starting Date, Ending Date, “y”)

It calculates the number of years between two days.

Furthermore, there is another format of the DATEDIF function. Which calculates the number of days between two dates neglecting the years. That means, it counts the days of the same year.

For example, if we take the Starting Date as June 11, 2012, and the Ending Date as September 22, 2020. It will count only the number of days between June 11, 2012, and September 22, 2012.

Here, the format is as below.

=DATEDIF (Starting Date, Ending Date, “yd”)

Also, you can see the attached image below.

Similarly, there is one more format.

=DATEDIF (Starting Date, Ending Date, “ym”)

It calculates the number of months between two dates neglecting the years.

And, the last one is as below.

=DATEDIF (Starting Date, Ending Date, “md”)

It calculates the number of days between two dates neglecting both months and years.

Note: DATEDIF is a hidden function in Excel. Actually, you will not find it anywhere in Excel Toolbar. So, you have to write the full name in the cell or Formula Bar to get it.

4. Counting Net Working Days Excluding Weekends in Excel

Now we will count the total number of workdays between two days. For this, we will use two functions. They are:

  • NETWORKDAYS Function
  • NETWORKDAYS.INTL Function

4.1. Use of NETWORKDAYS Function to Count Work Days from Date

Here, we will use the NETWORKDAYS function to count work days from Date. Basically, it takes three arguments, the Starting Date, the Ending Date, and a list of Non – Working Days or Holidays. Also, it takes Saturday and Sunday of each week as Weekends. Then it gives the number of total Working Days as output. Look at the image below. We have made a list of all holidays of the year in column G.

Use of NETWORKDAYS Function to Count Days from Date

  • Then, we entered the formula in cell E5.
=NETWORKDAYS(C5,D5,$G$5:$G$17)
  • Subsequently, press ENTER.

Here, we get the total Work Days as 2272 days.

  • And then dragged the Fill Handle icon to AutoFill the rest of the cells.

Lastly, we got all the work days.

Note: We have used the Absolute Cell Reference of the list of holidays because we do not want it to be changed while dragging the Fill Handle icon.

4.2. Employing NETWORKDAYS.INTL Function in Excel

The only difference between NETWORKDAYS and NETWORKDAYS.INTL function is that in NETWORKDAYS, the weekend holidays are fixed as Saturday and Sunday. But in NETWORKDAYS.INTL you can take it as you wish.

So NETWORKDAYS.INTL has four arguments, the Starting Date, the Ending Date, a Weekend Number, and a list of Holidays. Excel has fixed Weekend Numbers. In the image below, columns I and J contain the list of the Weekend Numbers.

Employing NETWORKDAYS.INTL Function to Count Days from Date

Let us think for a moment that in Tata Group, the weekly holidays are Friday and Saturday. So the Weekend Number is 7.

  • Now, we go to column E and insert this formula in cell E5.
=NETWORKDAYS.INTL(C5,D5,7,$G$5:$G$17)
  • Then, we click ENTER.

  • And then we drag the Fill Handle icon through the column.

Finally, we get the total number of Working Days of each employee considering Friday and Saturday as holidays.

Note: We have again used Absolute Cell Reference for the list of Holidays because we do not want it to increase while dragging the Fill Handle icon.

5. Using Combined Functions to Count Days from Date in Excel

You can employ a combination of some functions like the DATE function, YEAR function, MONTH function, and DAY function as an Excel formula to count the number of days from the date. The steps are given below.

  • Firstly, you have to select a new cell E5 where you want to keep the result.
  • Secondly, you should use the formula given below in the E5 cell.
=DATE(YEAR(D5),MONTH(D5),DAY(D5))-DATE(YEAR(C5),MONTH(C5),DAY(C5))
  • Finally, press ENTER to get the result.

Use of Combination of Functions to Count Days from Date in Excel

Formula Breakdown

  • Firstly, DAY(C5)—> The DAY function will return the day number from the C5 cell.
    • Output—> 11.
  • Secondly, MONTH(C5)—> The MONTH function will return the day number from the C5 cell.
    • Output—> 8.
  • Thirdly, YEAR(C5)—> The YEAR function will return the day number from the C5 cell.
    • Output—> 2011.
  • Fourthly, DATE(YEAR(C5),MONTH(C5),DAY(C5))—> This DATE function will return the date.
    • Output—> 11-08-11.
  • Similarly, DATE(YEAR(D5),MONTH(D5),DAY(D5))—> returns 24-04-20.
  • Finally, (24-04-20)-(11-08-11)—> becomes 3179.

  • And then we drag the Fill Handle icon through the column.

Lastly, we get the total number of days for all the employees.


Count Days Between Today and Another Date in Excel

Moreover, we can count the days between today and another date using the TODAY function. Basically, this TODAY function returns the current date. So, you can count the days from the current date. Now, we will see two ways for doing this.


1. Use of TODAY Function in Excel

You can employ only the TODAY function as an Excel formula to count the number of days from date. The steps are given below.

  • Firstly, you have to select a new cell D5 where you want to keep the result.
  • Secondly, you should use the formula given below in the D5 cell.
=TODAY()-C5
  • Finally, press ENTER to get the result.

Use of TODAY Function in Excel to Count Days from Date

  • Then, we drag the Fill Handle icon through the column.

Lastly, we get the total number of days for all the employees.


2. Employing TODAY & DAYS Functions in Excel

You can apply both the TODAY and DAYS functions as an Excel formula to count the number of days from the date. The steps are given below.

  • Firstly, you have to select a new cell D5 where you want to keep the result.
  • Secondly, you should use the formula given below in the D5 cell.
=DAYS(TODAY(),C5)
  • Finally, press ENTER to get the result.

Employing TODAY & DAYS Functions to Count Days from Date

  • Then, we drag the Fill Handle icon through the column.

Lastly, we get the total number of days for all the employees.


Practice Section

Now, you can practice the explained method by yourself.

Practice Section for Excel Formula to Count Days from Date

➥ Read More: How to calculate working days in Excel excluding weekends & holidays


Conclusion

Using the above methods, we can comfortably calculate the number of days or workdays between any two dates using Microsoft Excel. Do you know any other method? Let us know in the comment section.


Further Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo