How to Use Excel to Calculate Between Dates

Dan Price 01-09-2017

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”)

How to Use Excel to Calculate Between Dates excel date day


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, “
    &DATEDIF(D2,E2,”md”)&” days”

How to Use Excel to Calculate Between Dates excel date range

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 3 Crazy Excel Formulas That Do Amazing Things Excel formulas have a powerful tool in conditional formatting. This article covers three ways to boost productivity with MS Excel. Read More do you know about? Let us know in the comments.

Affiliate Disclosure: By buying the products we recommend, you help keep the site alive. Read more.

Whatsapp Pinterest

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. Shirley
    September 1, 2017 at 3:23 pm

    Great tips....learn something new and the Ctrl+Shift+U was immediately put into use. Thanks.