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`

function is a popular formula that has 3 parameters which are all required. If you [encounter any error]({{< relref “#datedif-errors-and-problems” >}}) 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 | D | |
---|---|---|---|---|

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

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

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

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 the`start_date`

you will get an`#NUM!`

error. Make sure your`end_date`

occurs**on or after**the`start_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 | D | |
---|---|---|---|---|

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

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

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

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

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

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

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 | D | |
---|---|---|---|---|

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

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

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

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

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]({{< relref “#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 | D | |
---|---|---|---|---|

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

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

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

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

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

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

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

As previously noted with the [difference in days]({{< relref “#date-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

If you want to calculate the difference between two dates in whole months you can set the third parameter to the `DATEDIF`

formula to `"YM"`

.

A | B | C | D | |
---|---|---|---|---|

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

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

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

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

The results from this result set above are fairly simple to understand and are consistent with what we already know about – the day of the month needs to be matched or eclipsed to increment the value.

### Date Difference in Days Irrespective of Years

Similar to our previous example with [date differences in days irrespective of months]({{< relref “#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 | D | |
---|---|---|---|---|

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

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

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

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.

## 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 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 | D | |
---|---|---|---|---|

1 | 1 June 2020 | 11 December 2020 | `=B1-A1` | 193 |

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

3 | 1 June 2020 8:00:00 | 11 December 2020 10:00:00 | `=B3-A3` | 193.083333 |

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

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 | `=TRUNC(B5-A5)` | 193 |

## 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]({{< relref “#date-difference-in-days” >}}) 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 | `=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.

{{< figure src=”/images/datedif-check-locale.png” caption=”Check spreadsheet locale settings” alt=”Google Sheets locale setting” >}}

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.

## Conclusion

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]({{% relref “datedif-docs” %}}).