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 Office 365 version.
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. I have written, 09-03-11.
- Now, go to the Home >> Number section in the Ribbon. You will see the Date option is automatically selected thereby.
- 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.
- You can see various Date formats under the Type menu. 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.
Excel Formula to Count Days from Date: 5 Easy Methods
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, Starting Dates, and Ending Dates in columns B, C, and D respectively.
Here, the CEO of the company wants to find out the number of total days each employee worked. Now, we are showing the ways to do that.
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 the date. Additionally, we are going to use the following formula.
- First, select a column where you want to have the total days. Here, we have selected column E and named it Total Days.
- Here, we have selected cell E5, the first cell of this column, and inserted the corresponding formula.
=D5-C5
- Next, press ENTER.
Excel has calculated the total number of days between the two dates, 3179.
- Now, 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.
As a result, 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
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.
- Here, we are again selecting the E5 cell.
- Next, write down the formula.
=DAYS(D5,C5)- Lastly, click ENTER.
See we have got the number of days, 3179.
- Like the previous one, drag the Fill Handle icon and fill all the cells of the column with the same formula.
Read More: Excel Calculates Difference Between Two Dates in Days
3. Counting Days by Excel’s DATEDIF Function
Here, you may use the DATEDIF function in an Excel formula to count the number of days from dates. Additionally, the structure of this function is as below.
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.
- Here, we have selected column E and named it Total Days.
- Next, select cell E5 as the first cell of the column.
- After that, write the corresponding formula in cell E5.
=DATEDIF(C5,D5,"d")- Subsequently, press ENTER.
See we have got the number of days, 3179.
- 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.
It calculates the number of months between two dates.
Another format is:
It calculates the number of years between two dates.
Furthermore, there is another format of the DATEDIF function. This 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.
Also, you can see the attached image below.
Similarly, there is one more format.
It calculates the number of months between two dates neglecting the years.
And, the last one is as below.
It calculates the number of days between two dates neglecting both months and years.
Read More: How to Calculate 90 Days from a Specific Date in Excel
4. Counting Net Working Days Excluding Weekends in Excel
Now we will count the total number of workdays between the two dates. 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 dates. 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 the default weekend. 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.
- Then, we entered the formula in cell E5.
=NETWORKDAYS(C5,D5,$G$5:$G$17)- Press ENTER.
Here, we get the total Work Days as 2272 days.
- Then drag the Fill Handle icon to AutoFill formula to the rest of the cells.
4.2. Employing NETWORKDAYS.INTL Function in Excel
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.
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 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.
- 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.
Read More: How to Find Number of Weeks Between Two Dates in Excel
5. Using Combined Functions to Count Days from Date in Excel
You can employ a combination of some functions like the DATE, YEAR, MONTH, and DAY functions in an Excel formula to count the number of days from the dates.
- 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))- Finally, press ENTER to get the result.
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.
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 from a date to today 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 of doing this.
1. Use of TODAY Function in Excel
You can employ only the TODAY function in an Excel formula to count the number of days from dates. The steps are given below.
- Select a new cell D5.
- Insert the formula given below in cell D5.
=TODAY()-C5- Finally, press ENTER to get the result.
- Then, we drag the Fill Handle icon through the column.
Read More: Excel Formula to Calculate Number of Days Between Today and Another Date
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.
- Select cell D5 and insert the formula given below.
=DAYS(TODAY(),C5)- Finally, press ENTER to get the result.
- Then, we drag the Fill Handle icon through the column.
Practice Section
Now, you can practice the explained method by yourself.
Download Practice Workbook
You can download the practice workbook from here:
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.
