There might be a lot of reasons that you need to calculate the number of days between two dates in Excel or the number of weeks, months, and years between two dates in Excel. If you have been given a task which you have to complete within a fixed time, knowing the number of workdays or weekends is a smart thing. In this article on how to calculate number of days between two dates in Excel, we will demonstrate different formulas and functions for calculating the number of days, weeks, years, weekends and workdays in Excel.
Download Practice Workbook
6 Ways to Calculate Number of Days Between Two Dates in Excel
Excel offers various methods to calculate days between two dates. To use these formulas, we have made a dataset named Joining and Contract Renewal Date of Some Employees. It has column headers as Name of Employee, Joining Date, and Contract Renewal Date. The dataset is like this.
1. Using Subtraction Formula
Subtraction is quite a simple method to calculate days between two dates. In this method, you need to subtract the two dates directly in Excel. To execute the method, please follow the steps below.
- Firstly, write the formula in the E5
Here, D5 and C5 are the Contract Renewal Date and Joining Date of Jane respectively.
- Secondly, press ENTER to find the output as 23.
- Thirdly, use Fill Handle by dragging the cursor downwards while holding the cursor on the right-bottom corner of the E5 cell like this.
- Eventually, we’ll get all the outputs like this.
2. Utilizing DATEDIF Function
We can use the DATEDIF function to find years, months, or days between two dates. However, we’ll use it here to find days.
- To show this, firstly write the formula in the E5 cell like this.
- Secondly, press ENTER and use Fill Handle to get all the outputs like this.
3. Applying DAYS Function
The DAYS function is one of the core functions to calculate days between two dates.
- We can write the following formula in the E5 cell like this.
- Similarly, press ENTER, and after that, by using the Fill Handle we get all other outputs like this.
4. Using TODAY Function
We can use the TODAY function to calculate days. The TODAY function automatically gives the date of today and it changes according to the change of today’s date. Later, we need to use the DATEDIF function to calculate days.
- We can write the TODAY function in the formula like this.
- If we press ENTER and then use Fill Handle, we’ll find today’s date in the output cells.
- Secondly, we need to write the following formula in the F5
Here, D refers to Date. We can also use M or Y to find Months or Years respectively.
- Lastly, press ENTER and use Fill Handle to get all the outputs on days like this.
- How to Add Dates in Excel Automatically (2 Simple Steps)
- Minus Number of Days or a Date from Today in Excel
- How to Add Years to a Date in Excel (3 Easy Ways)
- Calculate Number of Months Between Two Dates in Excel
5. Using NOW Function
We can also use the NOW function instead of the TODAY function. Basically, these two functions work in the same way and give the same output.
- Initially, we can use the NOW function in the D5 cell like this.
- Eventually, press ENTER.
- Similarly as before, use the Fill Handle.
- Secondly, we need to write the DATEDIF function in the E5 cell like this.
- Finally, use the Fill Handle after pressing ENTER to get the outputs like this.
6. Applying DATEDIF and TODAY Functions
- We can use the combination of DATEDIF and TODAY functions instead of using those individually.
Firstly, write the formula in the D5 cell like this.
- Lastly, as before, press ENTER and use the Fill Handle.
Calculate Number of Days Between Two Dates Excluding Weekends
The NETWORKDAYS function is used when we need to calculate the working days between two dates. The NETWORKDAYS function automatically removes the vacations from the calculation. You can easily find the number of total working days between 2 dates by using the NETWORKDAYS and NETWORKDAYS.INTL functions. The difference between these two functions is that the NETWORKDAYS is designed to exclude Saturday and Sunday as weekend days by default but in the NETWORKDAYS.INTL functions you can define the weekends.
- We can show this formula by writing in the E5 cell like this.
- Similarly, as before, press ENTER Then use the Fill Handle to get the outputs.
- Here, we can see that in the outputs the holidays are eliminated from calculations.
Add/Subtract Days Excluding Weekends
We can add/subtract days excluding weekends by using the NETWORKDAYS.INTL function.
Here, in the D Column we have put the Days Need to be Added with the Joining Date to find out the Contract Renewal Date of the employees.
- Firstly, write the formula like this.
Here, 7 refers to the type of weekend we need to take into account. In the formula 7 refers to Friday and Saturday as weekend.
- Secondly, press ENTER and use Fill Handle to find all the outputs.
- For defining the weekends in the NETWORKDAYS.INTL formula we can follow the following table.
Calculate Number of Weekends
We can calculate number of weekends by using the combination of SUM, INT and WEEKDAY functions. To show this write the formula in the E5 cell like this.
- Eventually, press ENTER and use the Fill Handle.
We can find days between two days easily if we study this article properly. Please feel free to visit our official Excel learning platform ExcelDemy for further queries.
- How to Add Days to a Date in Excel Excluding Weekends (4 Ways)
- Calculate Years and Months between Two Dates in Excel (6 Approaches)
- How to Add 3 Months to a Date in Excel (4 Easy Methods)
- [Fixed!] VALUE Error (#VALUE!) When Subtracting Time in Excel
- How to Calculate Working Days between Two Dates in Excel (4 Methods)