The
DATEDIF
function calculates the number of periods between two dates. The best way to remember this function is it calculates the
DATE DIF
ference between two dates.
What Is
DATEDIF
?
The
DATEDIF
formula calculates the difference between two dates according to a third parameter determining the type of difference needed, for example, days, months, years (etc).
The
DATEDIF
function is a popular formula that has 3 parameters that are all required. If you encounter any error when using this function check you have populated all three fields correctly.
The function reference is as follows:
=DATEDIF(start_date, end_date, unit)
The
start_date
parameter should be the earliest date, the
end_date
being the latest date, and the
unit
is the value of the periods you want returned from the difference between those two dates.
Accepted values in Google Sheets for the
unit
value are:
Date Difference In Days
By setting the third parameter to the
DATEDIF
formula to
D
it means the resulting value returned will calculate the number of
days
between the two dates. Here are some examples:
A  B  C  

1  1 June 2020  1 June 2020 
0
=DATEDIF(A1, B1, "D")

2  1 June 2020  2 June 2020 
1
=DATEDIF(A2, B2, "D")

3  1 June 2020  31 May 2020 
#NUM!
=DATEDIF(A3, B3, "D")

DATEDIF
and
"D"
as the third parameter
Several things to note about these tests when using the
DATEDIF
function and the
unit
parameter as
D
:
 The dates are not inclusive. Notice the first row is 0, if it were inclusive it would have been 1.

Should the
end_date
come before thestart_date
you will get an#NUM!
error. Make sure yourend_date
occurs on or after thestart_date
.
Date Difference In Months
If you want to calculate the difference between two dates in months, then set the third parameter of the formula to
"M"
, here are some interesting examples of what this looks like for certain values:
A  B  C  

1  29 February 2020  29 February 2020 
0
=DATEDIF(A1, B1, "M")

2  29 February 2020  28 March 2020 
0
=DATEDIF(A2, B2, "M")

3  29 February 2020  29 March 2020 
1
=DATEDIF(A3, B3, "M")

4  29 February 2020  1 March 2021 
12
=DATEDIF(A4, B4, "M")

5  31 January 2020  29 February 2020 
0
=DATEDIF(A5, B5, "M")

6  31 January 2020  1 March 2020 
1
=DATEDIF(A6, B6, "M")

DATEDIF
and
"M"
as the
unit
parameter
As can be seen from the table above, the
day of the month
needs to be either
eclipsed or matched
for the returning value to increment. In
rows 5 and 6
especially we can see the days in the month of February 2020 would not have eclipsed the
start_date
of 31st January 2020, but when the
end_date
advances into the next month (1st March) the increment occurs.
Therefore, if your
start_date
is the end of a month containing 31 days it will not be until the 1st day of the following month, preceding a month containing only 28, 29 or 30 days, that the value will be incremented.
Date Difference In Years
If you want to calculate the difference between two dates as years then set the third parameter value to
"Y"
. Here is what this would look like based on the following inputs:
A  B  C  

1  29 February 2020  29 February 2020 
0
=DATEDIF(A1, B1, "Y")

2  29 February 2020  28 February 2021 
0
=DATEDIF(A2, B2, "Y")

3  28 February 2020  28 February 2021 
1
=DATEDIF(A3, B3, "Y")

4  29 February 2020  1 March 2021 
1
=DATEDIF(A4, B4, "Y")

DATEDIF
and
"Y"
as the
unit
parameter
As can be seen from the examples above, it’s not until the date and month of the
start_date
has been eclipsed or matched the value returned by the
DATEDIF
formula increments. This is very similar to when calculating the date difference in months.
Date Difference In Days Irrespective of Months
If you need to calculate the difference in days between two dates irrespective of the difference between months then you’ll need to use
"MD"
as the value of the third parameter, as demonstrated below:
A  B  C  

1  31 January 2020  31 January 2020 
0
=DATEDIF(A1, B1, "MD")

2  31 January 2020  29 February 2020 
29
=DATEDIF(A2, B2, "MD")

3  31 January 2020  1 March 2020 
1
=DATEDIF(A3, B3, "MD")

4  31 January 2020  2 March 2020 
0
=DATEDIF(A4, B4, "MD")

5  31 January 2020  30 March 2020 
27
=DATEDIF(A5, B5, "MD")

6  31 January 2020  31 March 2020 
0
=DATEDIF(A6, B6, "MD")

7  31 January 2020  1 April 2020 
1
=DATEDIF(A7, B7, "MD")

DATEDIF
and
"MD"
as
unit
parameter
As previously noted with the difference in days we can see from the examples above the difference in days is not inclusive. However, there are some interesting results:
Row 3 shows a result of
1
.
Why?
This implies there was a day short in the previous month of getting to the same date, and the current date of 1st March 2020 is 1 day short of the 31 days (29 days in February + 1 day in March = 30 days – 31 days = 1). This then puts the remaining values of that month (in March) out, until we hit the new month April.
Date Difference In Whole Months Irrespective Of Years
If you want to calculate the difference between two dates in
whole months
irrespective of the difference in years you can set the third parameter to the
DATEDIF
formula to
"YM"
.
A  B  C  

1  31 January 2020  31 January 2022 
0
=DATEDIF(A1, B1, "YM")

2  31 January 2020  29 February 2020 
0
=DATEDIF(A2, B2, "YM")

3  31 January 2020  1 March 2020 
1
=DATEDIF(A3, B3, "YM")

4  31 January 2020  31 March 2020 
2
=DATEDIF(A4, B4, "YM")

DATEDIF
and
"YM"
as
unit
parameter
As you can see from the first row in the spreadsheet result above even though the year of the second date in column B is 2022 the result is still
0
as the difference in dates is compared irrespective of the year.
The remaining results are consistent with what we already know about when calculating the difference in months .
Date Difference In Days Irrespective of Years
Similar to our previous example with date differences in days irrespective of months we have a similar calculation but this time irrespective of years, here are some examples:
A  B  C  

1  29 February 2020  29 February 2020 
0
=DATEDIF(A1, B1, "YD")

2  29 February 2020  28 February 2021 
365
=DATEDIF(A2, B2, "YM")

3  29 February 2020  1 March 2020 
0
=DATEDIF(A3, B3, "YM")

DATEDIF
and
"YD"
as
unit
parameter
Consistent with the days’ difference irrespective of months we see that where the start date isn’t matched by day of month and month, it calculates the difference the previous month fell short.
DATEDIF
Quarters
How do you calculate the difference between two dates in quarters? Recently I had a need where I had to calculate the remaining payments on a lease that was being paid once every 3 months. There were a couple of methods of approaching it:
 Calculate the number of days between the two dates and divide by 90 or 91.25.
 Calculate the number of months between the two dates and divide by 3.
Here’s how both approaches worked for me:
A  B  C  

1  29 February 2020  28 February 2022 
8.11111111
=DATEDIF(A1, B1, "D")/90

2  29 February 2020  28 February 2022 
8
=DATEDIF(A2, B2, "D")/91.25

3  29 February 2020  28 February 2022 
7.6666667
=DATEDIF(A3, B3, "M")/3

4  29 February 2020  1 March 2022 
8
=DATEDIF(A3, B3, "M")/3

DATEDIF
As you can see from the above examples, the most precise calculation is using
DATEDIF
with the
unit
parameter set to
"M"
and dividing that result by
3
rather than the alternative method of using
"D"
and dividing by 90 or 91.25.
Therefore, when calculating the difference between two dates to find the number of quarters between two dates use the formula:
=DATEDIF(start_date, end_date, "M")/3
If you only want to know the
whole number
of quarters that have occurred between the two dates then you could wrap the function in
TRUNC
like so:
=TRUNC(DATEDIF(start_date, end_date, "M")/3)
The
TRUNC
function just removes any decimal portion from your result, therefore if used in cell
C3
in the spreadsheet above instead of producing a result of
7.6666667
it would just produce
7
.
Benefits of
DATEDIF
Over Subtracting Two Dates
An objection to using the
DATEDIF
formula is that it achieves the same result when subtracting dates, which is certainly far simpler than trying to remember a formula, and it’s parameter values.
The biggest benefit of the
DATEDIF
formula is that it helps to calculate the difference between two dates (such as finding someone’s age)
by providing us with the
type
difference we need. Therefore, the
DATEDIF
formula is more versatile for
various
use cases.
Another minor benefit to using the
DATEDIF
function when calculating the difference in
days
between two dates over the simple subtraction method is if the input dates contain time.
A  B  C  

1  1 June 2020  11 December 2020 
193
=B1A1

2  1 June 2020  11 December 2020 
193
=DATEDIF(A2, B2, "D")

3  1 June 2020 8:00:00  11 December 2020 10:00:00 
193.083333333
=B3A3

4  1 June 2020 8:00:00  11 December 2020 10:00:00 
193
=DATEDIF(A4, B4, "D")

DATEDIF
formula
You can still achieve the same answer as the
DATEDIF
function, but you would need to remember and apply to use the
TRUNC
formula to achieve the same answer in row 3 above, like so:
A  B  C  D  

5  1 June 2020 8:00:00  11 December 2020 10:00:00 
193
=TRUNC(B5A5)

193 
TRUNC
formula instead of
DATEDIF
to find the difference in days between 2 dates
DATEDIF
Errors and Problems
There are a couple of things to be mindful of when using the
DATEDIF
function. As already shown above in the
DATEDIF
days examples we had a result on the last row that gave us an error.
If you do get a
#NUM!
error check the parameter values are correct, and the earliest date is the
start_date
value, and the later date is the
end_date
value.
Provided your parameter values have been set correctly, the only other type of error which may cause problems and may not be as obvious is if the locale setting of your dates are not what you anticipated.
Incorrect
DATEDIF
Locale
If you’re getting an answer which definitely isn’t correct you will want to check the settings of your spreadsheet to ensure it correctly interprets a date field value.
For example, some countries, such as the UK and Australia, have dates set to the following format:
DD/MM/YYYY
whereas other countries, such as Canada and the Unites States, have date values set to the format of:
MM/DD/YYYY
.
A  B  C  D  

1  01/06/2020  11/12/2020 
193 or 311?
=DATEDIF(A1, B1, "D")

193
for
DD/MM/YYYY
folks, or
311
for
MM/DD/YYYY
folks!
Ways to minimise this error occurring would be to quickly check your locale settings are correct before starting a new spreadsheet, or when modifying a spreadsheet.
In a Google Sheet this would be simply going to File > Spreadsheet Settings > Locale – change this to your country.
Another way to check is to input a date that wouldn’t be a date in the other format, for example, try
01/13/2020
and
13/01/2020
– one of these will not work and your
DATEDIF
formula will return a
#VALUE!
error as the date fields entered aren’t actual dates.
Summary
In this article, we’ve done a deep dive into the
DATEDIF
formula and seen the results it provided based on different parameter values.
We’ve also seen how the formula can be better suited than simply subtracting two dates, and we’ve diagnosed our problems and fixed them.
Overall the
DATEDIF
function is a fantastic function to use, and you should look at incorporating it more in your own daily use. If you’d like to learn more about the function in Google Sheets, you can read the documentation.