Calculating the number of days between two dates is a common operation in Excel. Whether you’re planning a project timeline, tracking age, or analyzing trends over time, knowing how to find the days between dates in Excel provides powerful abilities for date calculations.
Fortunately, Excel offers a variety of simple methods to get the days between two dates. Read on to learn different formulas and functions for calculating date differences in Excel.
Why Calculate Days Between Dates in Excel
Here are some examples of situations where calculating days between dates is useful:
- Finding how many days an employee has worked at a company
- Determining age in days for legal documents
- Creating dynamic deadlines based on a fixed start date
- Analyzing shipping times and metrics
- Scheduling tasks and milestones in a project plan
- Tracking cycles and repetitions
- Building visualizations like calendars and Gantt charts
- Performing date-based arithmetic for metrics and KPIs
Whether it’s basic date math or more advanced time-sensitive analysis, Excel provides the flexibility to handle many date difference needs.
Calculate the Total Days Between Dates
The most straightforward way to get the total days between two dates in Excel is to simply subtract the start date from the end date.
For example:
=EndDate – StartDate
Since Excel stores dates as serial numbers, subtracting one date from another will return the number of whole days between them.
This method works for dates in the past or future. It correctly handles situations where the start date is more recent than the end date, returning a negative number of days.
Some examples:
=A2-A1 -> Days between dates in A1 and A2
=A2-TODAY() -> Days between A2 and today
=TODAY()-A1 -> Days from A1 to today
This date subtraction gives you complete flexibility to calculate days between any two dates in Excel.
Use the Excel DAYS Function
The DAYS function is a specialized function for getting total days between dates. It was introduced in Excel 2013, so requires a relatively modern version.
The DAYS function takes two dates and returns the number of days between them:
=DAYS(EndDate, StartDate)
A few things to note with the DAYS function:
- The order of dates is reversed from the subtraction method
- It can only handle EndDate later than StartDate (otherwise returns #VALUE error)
- The result doesn’t count an extra day if StartDate and EndDate are 1 day apart
While limited compared to subtraction, DAYS can be useful due to the dedicated purpose of calculating days between dates.
DATEDIF Function for Date Differencing
Excel’s DATEDIF function calculates the difference between two dates in various units like years, months and days.
To get total days between dates, use:
=DATEDIF(StartDate, EndDate, “D”)
DATEDIF shares some similarities with DAYS:
- The order of dates is StartDate then EndDate
- It can’t calculate days if StartDate is later than EndDate
- The result doesn’t count an extra day if StartDate and EndDate are 1 day apart
DATEDIF is more versatile for calculating differences in other units, but DAYS is simpler if you only need total days.
Network Days Between Dates
The above methods all calculate the total calendar days between dates. To exclude weekend days, use the NETWORKDAYS function.
NETWORKDAYS counts only workdays between a start and end date:
=NETWORKDAYS(StartDate, EndDate)
- This gives only business days Monday-Friday, excluding weekend days.
- You can optionally add a holiday list as the 3rd argument to further exclude dates like holidays.
Calculate Years, Months, or Hours Between
In addition to total days between dates, you can calculate other units of time difference:
Years:
=DATEDIF(StartDate, EndDate, “Y”)
Months:
=DATEDIF(StartDate, EndDate, “M”)
Hours:
=DATEDIF(StartDate, EndDate, “h”)
DATEDIF allows getting the time difference in years, months, and even hours.
You can also use the YEAR, MONTH, and HOUR functions for specialized calculations of those units between dates.
Keyboard Shortcuts for Date Calculations
When working extensively with date calculations, use these shortcuts to insert the current date and time:
Ctrl + ; = TODAY()
Ctrl + Shift + : = NOW()
You can quickly build date formulas by combining these shortcuts with cell references.
Error Handling for Invalid Dates
If one of the dates is missing or improperly formatted, your formula may return unexpected errors.
Wrap your formulas in IFERROR to provide a graceful result:
=IFERROR(DAYS(EndDate, StartDate),””)
This displays a blank for any errors instead of unsightly #VALUE! or #NUM! errors.
Conclusion
Calculating the number of days between dates is a common need in many business and personal situations. Excel provides a variety of functions and formulas for simple to advanced date difference math.
The most flexible method is to simply subtract one date from another, which properly handles past, future, and reversed dates. For specialized cases, use functions like DAYS, DATEDIF, and NETWORKDAYS.
Combine date functions creatively to solve your specific days between dates needs. And leverage Excel’s powerful date calculation abilities to gain new insights!
