A reader contacted me recently with a deceptively simple Microsoft Excel question: “How do I calculate the difference between two dates?”

I say “deceptively simple” because the answer depends upon the context, namely, whether the two dates being compared are actually embedded in cells within the Microsoft Excel spreadsheet.

If they are, then the formula needed to perform the calculation is actually very simple (assuming you already know how to do simple formulas in Microsoft Excel):

If, however, the dates are not anywhere else in the spreadsheet, you’ll have to embed them in the formula itself. This is done using the DATEVALUE function:

What a lot of people don’t realize is that Microsoft Excel doesn’t actually store dates per se. It stores each date as a number calculated as the number of days from 1/1/1900, so that January 1, 1900 is “1” and all subsequent dates are successively higher numbers. The DATEVALUE function instructs Excel to convert the date back to its original number form in order to perform the arithmetic operation, in this case calculating the number of days between the two dates.

So the next time you find yourself needing to calculate the difference between the two dates, use a formula like one of those illustrated above.

Deborah Savadra

I spend an inordinate amount of my time playing with computers and attempting to explain technology to lawyers and law office staff. It's not always easy, but someone's got to do it.

But, seriously, I'm a law firm software trainer by trade with nearly 30 years of experience in and around law firms and their technology. This blog is my attempt to spread the word about better and more efficient ways to use Microsoft Office in a legal practice context.

Similar Posts

Customizing the Status Bar

There’s a whole host of ways you can make the various Microsoft Office applications easier to use. In fact, most users don’t take full advantage of the options for customizing these applications to make the Office suite work better for them.

Today, we’re going to talk about one of the easiest customizations: the Status Bar.

4 Comments

  1. This is a great topic. Obviously, correctly calculating time periods is critical for law offices. I sometimes need months and years elapsed, too. So, I use this function:

    =DATEDIF([Start Date],[End Date],”[D/M/Y]”)

    “D” gives you the days elapsed. “M” and “Y” give you the months and years elapsed.

    I have a spreadsheet called AAACalculate that I use for this (and other) calculations. I label my A4 cell “Start Date” and my B4 cell “End Date.” The first function, =DATEDIF(A4,B4,”D”), goes in my C4 cell, which is labeled “Days Elapsed.” The second function, =DATEDIF(A4,B4,”M”), goes in my D4 cell, which is labeled “Months Elapsed.” The E4 cell has =DATEDIF(A4,B4,”Y”) for years elapsed.

    Pull up the spreadsheet, type in the start and end dates, and you have the days, months, and years elapsed between the two dates.

    1. Sorry to reply to my own comment. I wanted to let you know that I found this function a long time ago. The function still seems to work OK, but it is not a “documented” function for newer versions of MS Excel.

  2. Pingback: Like Copyblogger, I’m turning off Comments. Here’s why.

Comments are closed.