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

How to Add Dates in Excel

Click on a cell and write any date inside it, in the conventional format, DD/MM/YYYY.

Go to the Home and Number section in the Ribbon. You will see the Date option is automatically selected.

If you want to change the format, select the dropdown menu. You will get options like General, Number, Currency, Percentage, etc.
Select the last option, More Number Formats.

At this time, you will get a dialogue box named Format Cells.
Go to the Date formats.
Under the Type menu on the right, click on the one you like. We chose March 14, 2012.
Click OK.

Excel Formula to Count Days from Date: 5 Easy Methods

Let’s consider the following dataset with employee records of a company named Tata Group. It contains Employee Names, Starting Dates, and Ending Dates in columns B, C, and D respectively. Let’s find the total number of days each employee worked.

Method 1 – Using Normal Subtraction to Count Days from a Date in Excel

Select a column where you want to have the total days. We have selected column E and named it Total Days.
Select cell E5, the first cell of this column, and insert the corresponding formula:

=D5-C5

Press Enter.

To find out the total days of all the employees, drag the Fill Handle icon (plus (+) sign on the bottom right corner) or double-click it.

You will find all the cells filled with the formula and the number of days.

Method 2 – Applying the DAYS Function to Count Days from a Date in Excel

Select the E5 cell.
Copy the following formula into it.

=DAYS(D5,C5)

Press Enter.

Drag the Fill Handle icon and fill all the cells of the column with the same formula.

Note: The DAYS function is available from Excel 2013.

Method 3 – Counting Days with Excel’s DATEDIF Function

The structure of this function is:

=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 the Starting Date as the first argument, while DAYS takes the Ending Date first.

Select column E and named it Total Days.
Select cell E5 as the first cell of the column.
Copy the corresponding formula in cell E5:

=DATEDIF(C5,D5,"d")

Press Enter.

Drag the Fill Handle icon and fill all the cells of the column with the same formula.

You can also change the third argument of the function.

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

This calculates the number of months between two dates.

Another format is:

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

It calculates the number of years between two dates.

You can use the argument “yd” to ignore the years. This means that the formula will calculate the difference between the dates as if they were on the same year.

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

Also, you can see the attached image below.

Similarly, ym calculates the month difference by ignoring the years.

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

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

Finally, you can use “md” as an argument.

=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. You will not find it anywhere in the Ribbon. So, you have to write the full name in the cell or Formula Bar to get it.

Method 4 – Counting Net Working Days Excluding Weekends in Excel

Case 4.1 – Using the NETWORKDAYS Function to Count Workdays from a Date

The function considers Saturday and Sunday as the default weekend but can also use an array of dates for additional holidays in a year. Then it gives the number of total Working Days as output.

We have made a list of all holidays of the year in column G.

Enter this formula in cell E5:

=NETWORKDAYS(C5,D5,$G$5:$G$17)

Press Enter.

Drag the Fill Handle icon to AutoFill the formula to the rest of the cells.

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.

Case 4.2 – Using the NETWORKDAYS.INTL Function in Excel

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.

Let’s consider that the weekend in Tata Group is Friday and Saturday. So, the Weekend Number is 7.

We kept the same holiday column as in the first case.
Go to column E and insert this formula in cell E5:

=NETWORKDAYS.INTL(C5,D5,7,$G$5:$G$17)

Press Enter.

Drag the Fill Handle icon through the column.

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.

Method 5 – Using Combined Functions to Count Days from a Date in Excel

Select a new cell E5 to keep the result.
Use the formula given below in cell E5.

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

Press Enter.

Formula Breakdown

DAY(C5)—> The DAY function will return the day number from cell C5.
- Output—> 11
MONTH(C5)—> The MONTH function will return the day number from cell C5.
- Output—> 8
YEAR(C5)—> The YEAR function will return the day number from cell C5.
- Output—> 2011
DATE(YEAR(C5),MONTH(C5),DAY(C5))—> This DATE function will return the date.
- Output—> 11-08-11