How to Use Excel to Calculate Between Dates
Why is calculating dates in your head so difficult? It feels like one of those things that should be easy, but seven-day weeks, months with uneven numbers of days, and occasional leap years all conspire to wreak havoc with your math ability.
Don’t believe me?
How many days are there between January 9, 2015 and June 12, 2017? If you can figure it out in under five seconds, then okay, you’re clearly a genius. But if it takes you longer than that, then count yourself normal — and stop struggling when Excel can do the hard work for you.
Calculating the Number of Days Between Two Dates
On your Excel sheet, put the two dates you want to calculate in two separate cells.
You now have several equations available to you. Highlight the cell where you want the answer to be, and enter one of the following (make sure you replace the cell addresses with your own locations):
- Difference in days: =DATEDIF(D2,E2,”d”)
- Difference in weeks: =DATEDIF(D2,E2,”d”)/7
- Difference in months: =DATEDIF(D2,E2,”m”)
- Difference in years: =DATEDIF(D2,E2,”y”)
Note: With the exception of “weeks,” these calculations only give the number of completed days, months, or years. They won’t include partially-complete periods.
Calculating the Cumulative Time Between Two Dates
We know how to calculate total time periods, but what if we want to work out the total amount of time elapsed, listed as days, months, and years?
We need to combine the above equations. Put the following into one cell’s entry box and press Enter.
- =DATEDIF(D2,E2,”y”)&” years, “
&DATEDIF(D2,E2,”ym”)&” months, “
Note: Press Alt + Enter to add a new line and Ctrl + Shift + U to expand the box and see all the text.
Have you used Excel to calculate dates? What other useful equations do you know about? Let us know in the comments.