How to Calculate Inclusive Dates with the Excel DAYS Function
Question details
The user needs to calculate the number of days between two dates, including both the start and end dates, as the standard DAYS function only returns elapsed days.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating the total duration between two dates where both the starting and ending days must be counted.
- Observed behavior
- The DAYS function returns a result that is one day short of the user's expectation because it calculates elapsed days rather than an inclusive count.
Ensure that the cells containing your start and end dates are formatted as valid Excel Dates, not as Text, to prevent calculation errors.
Use the DAYS Function and Add 1 for Inclusive Counting
Adjust the standard DAYS formula to include both the start and end dates in your calculation.
The DAYS function is designed to calculate the elapsed time between two dates. For example, from March 1 to March 7 is 6 elapsed days. To count both March 1 and March 7, you must manually add 1 to the formula's result.
Click on the empty cell where you want the total number of days to appear.
Type the formula =DAYS(end_date, start_date)+1. For example, if your end date is in E8 and your start date is in D8, type =DAYS(E8,D8)+1.
Press Enter to apply the formula and view the inclusive day count.
Calculate Inclusive Days Using Simple Subtraction
Subtract the start date from the end date directly without using the DAYS function.
Calculate Dates Easily with WPS Spreadsheet
WPS Spreadsheet fully supports the DAYS function and all standard date calculations. It offers a highly compatible, user-friendly interface for managing your data and formulas.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your date data.
- 2. Enter the formula: Select the target cell and enter =DAYS(end_date, start_date)+1.
- 3. Get the result: Press Enter to get your inclusive date calculation instantly.
Frequently Asked Questions
Why does the DAYS function return a negative number?
The DAYS function syntax is =DAYS(end_date, start_date). If you accidentally put the start date first, the result will be negative. Swap the cell references to fix this.
What happens if my dates are formatted as text?
If your dates are stored as text, the DAYS function may return a #VALUE! error. You can fix this by selecting the cells, going to the Data tab, and using the Text to Columns feature to convert them to valid dates.
Can I use the DATEDIF function instead of DAYS?
Yes, you can use =DATEDIF(start_date, end_date, "d") to find the elapsed days. To make it inclusive, simply add +1 to the end of the formula, just like with the DAYS function.
