3 Suitable Excel Formula to Count Days from Date

Number of Days between Two Dates in Excel.

When working with dates in Excel, we often need to count the number of days between two dates 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 some easy excel formulas to count the number of days from date using Microsoft Excel.


Download Practice Workbook


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. Like I have written, 09/03/2011.

A Date in Excel

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

Date Option in 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. Select the last option, More Number Formats. You will get a dialogue box like this.

Number Format Box in Excel.

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

Date Formats in Excel

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

➥ Related: How to Add Dates in Excel Automatically


Excel Formula to Count Days from Date

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

A data set in Excel

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


1. Counting Days by Normal Subtraction

  • First of all, select a column where you want to have the total days. Then select the first cell of it.

Here I have selected column D and named it Total Days. Then I have selected the first cell of it, D4.

First Cell Selected of a Column

  • Then write the general subtraction formula =Ending Date – Starting Date. Click Enter. Here I am writing =C4 - B4.

A formula written in one cell of a column

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 (Small Plus (+) Sign on the bottom right corner) or double click it. You will find all the cells filled with the formula and having the number of days.

Dragging the Fill Handle in Excel


2. Counting Days by Excel’s DAYS Function

Excel provides a built-in Function called DAYS. It takes two arguments, the Ending Date and the Starting Date. And gives the total number of days in between as the output.

Again select the first cell of the column where you want to have the total days. Then write down the formula. After that, click Enter. Here I am again selecting D4 and writing =DAYS(C4,B4)

DAYS Function in Excel

See I have got the number of days, 3349. Now like the previous one, drag the Fill Handle and fill all the cells of the column with the formula.

Dragging the Fill Handle in Excel

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

=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.

DATEDIF Function in Excel

=DATEDIF (Starting Date, Ending Date, "m")

It calculates the number of months between two days.

DATEDIF Function in Excel

=DATEDIF (Starting Date, Ending Date, "y")

It calculates the number of years between two days.

DATEDIF Function in Excel

=DATEDIF (Starting Date, Ending Date, "yd")

It calculates the number of days between two dates neglecting the years. That means, it counts 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.

DATEDIF Function in Excel

=DATEDIF (Starting Date, Ending Date, "ym")

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

DATEDIF Function in Excel

=DATEDIF (Starting Date, Ending Date, "md")

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

DATEDIF Function in Excel

Note: DATEDIF is a hidden function in Excel. 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 Work Days by Excel’s NETWORKDAYS Function

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

  1. NETWORKDAYS
  2. NETWORKDAYS.INTL

Function 1: NETWORKDAYS

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. I have made a list of all holidays of a year in column F.

List of Holidays in Excel

Then I entered the formula in cell D4 =NETWORKDAYS(B4,C4,$F$4:$F$17). We get the total Work Days as 2272 days. And then dragged the Fill Handle to fill the rest of the cells.

NETWORKDAYS Function in Excel

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. 

Function 2: NETWORKDAYS.INTL

The only difference between NETWORKDAYS and NETWORKDAYS.INTL 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, the columns H and I contain the list of the Weekend Numbers.

Weekend Numbers in Excel

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

Now I go to column D and insert this formula in cell D4. D4 =NETWORKDAYS.INTL(B4,C4,7,F4:F16). Then I click Enter. And then I drag the Fill Handle through the column and get the total number of Working Days of each employee.

NETWORKDAYS.INTL Function in Excel

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.

➥ 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

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 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