Pinterest Stumbleupon Whatsapp
Advertisement

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

Advertisement

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”

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 3 Crazy Excel Formulas That Do Amazing Things I have always believed that Excel is one of the most powerful software tools out there. It's not just the fact that it's spreadsheet software. No, Microsoft Excel 2013 simply has an awesome collection of... Read More do you know about? Let us know in the comments.

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.