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.

Inserting Date for using Excel Formula to Count Days from Date

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

Dataset for using Excel Formula to Count Days from Date


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

Use of Normal Subtraction Excel Formula to Count Days from Date

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

Read More: Calculate Number of Days between Two Dates with VBA in Excel


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.

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

  • 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: The DAYS function is available from Excel 2013.

Read More: Excel Calculates Difference Between Two Dates in Days


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.

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

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

Read More: How to Calculate 90 Days from a Specific Date in Excel


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.

Use of NETWORKDAYS Function to Count Days from Date

  • Enter this formula in cell E5:
=NETWORKDAYS(C5,D5,$G$5:$G$17)
  • Press Enter.

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.

Employing NETWORKDAYS.INTL Function to Count Days from Date

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.

Read More: How to Find Number of Weeks Between Two Dates in Excel


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.

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

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
  • DATE(YEAR(D5),MONTH(D5),DAY(D5))—> returns 24-04-20
  • (24-04-20)-(11-08-11)—> becomes 3179

  • Drag the Fill Handle icon through the column.


Count Days Between Today and Another Date in Excel

 

Method 1 – Use the TODAY Function in Excel

  • Select cell D5.
  • Insert the formula given below in it:
=TODAY()-C5
  • Press Enter.

Use of TODAY Function in Excel to Count Days from Date

  • Drag the Fill Handle icon through the column.

Read More: Excel Formula to Calculate Number of Days Between Today and Another Date


Method 2 – Using TODAY and DAYS Functions in Excel

  • Select cell D5 and insert the formula given below:
=DAYS(TODAY(),C5)
  • Press Enter.

Employing TODAY & DAYS Functions to Count Days from Date

  • Drag the Fill Handle icon through the column.


Practice Section

You can practice these methods by yourself in the sample worksheet.

Practice Section for Excel Formula to Count Days from Date


Download Practice Workbook

You can download the practice workbook from here:


Related Articles


<< Go Back to Days Between Dates | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo