There might be a number of reasons why it would be handy to be able to calculate the difference between two dates in Excel or Google Sheets and I recently needed to do it when creating a sales report for a customer who wanted to know the average “Sales Cycle Length” for their sales team (i.e. the number of days it takes to close a deal). Other possible scenarios might be when calculating days worked on a project from a timesheet, or perhaps working out a person’s age from their date of birth.
There’s a handy hidden feature in excel that you can use to work this out easily, but you won’t find it in the formula bar and you also won’t get any instruction or hints on how to build out the argument. So you need to be confident enough to be able to write out the formula in full.
Now, there’s a reason for this and, according to the Microsoft Office Support site, its because this formula is only really provided for backward compatibility to older Lotus documents. The thing is though, it’s a really handy formula to have and much simpler than the alternative methods which include a more complicated sum approach.
So the formula we’re going to us is DATEDIF, which is used to get the number of days, months, or years between two dates.
As the Microsoft formula builder won’t help you to build this one, you’ll need to know the exact syntax to use, which is as follows:
=DATEDIF (start_date, end_date, unit)
Where start date must be BEFORE end date, otherwise it will result in an error.
And unit is the unit of time you want the results to be returned in (i.e. days, months or years)
There is a text code required to indicate the unit of time, and you can choose from any of the following:
“Y” = Difference in complete years
“M” = Difference in complete months
“D” = Difference in days
“MD” = Difference in days, ignoring months and years
“YM” = Difference in months, ignoring days and years
“YD” = Difference in days, ignoring years
Note: you must add the code into your formula in quotation marks as per the list above
How to build your formula
So to build you formula you will need to determine the start and end date. You can set these as “fixed” values by typing them into the formula directly, or of course can reference cells that contain this information.
In the example below, I have a table that lists the dates of first enquiry and date that the sale is made. I want to calculate the number of days between these two dates in order to assess the performance of our sales funnel.
So in the example below, the formula I will use is as follows
You’ll see that this then looks at the start date in column A, and works out the number of days between that and the date in column B, returning the answer in column C
You could also use a similar formula to work out somebody’s age – perhaps if you needed to be able highlight all children under a certain age, or flag up an important upcoming birthday
To work out somebody’s age in years we could use: =DATEDIF(A2,B2,”Y”)
Note: it’s worth noting that because this formula is not in general use, there are a couple of known issues with it. The Microsoft Help site mentions one particular know issues which is that using the “MD” unit in your formula might result in a negative number, a zero, or an inaccurate result. You’ll be happy to know there is a workaround for this! And you can read the full instruction here.