If you’re working with dates in Google Sheets and you need to find the difference between two dates, the DATEDIF function will come in handy.
In this tutorial, I will show you how to use the DATEDIF function in Google Sheets.
The DATEDIF function will take two dates and calculate the number of days, months, or years between them. Essentially this function calculates the difference between two dates.
The syntax of the function is:
=DATEDIF(start_date, end_date, unit)
- start_date – this is the first date to use in the calculation
- end_date – this is the second date to use in the calculation
- unit – this argument is where you specify the unit of time that you want to use for the calculation. Here are your options::
- “Y” – this will count the whole years between your two dates
- “M” – counts the number of whole months between your two dates
- “D” – the number of days between your two dates
- “MD” – this will count the number of days between your two dates after subtracting whole months
- “YM” – this will count the number of whole months between your two dates after subtracting whole years
- “YD” – counts the number of days between your two dates assuming they are not over a year apart
One important note to know about using this function is that months are only counted if they are equal to or go past the day. So if you want to count a full month, you have to use the first day of the next month for the full month to be considered
Using the function is very simple. Just put in your start date, end date, and the units that you want to count.
Here is an example of the different ways this can be used in your spreadsheet:
Let’s take a look at each of these formulas and break down what they do:
- =DATEDIF(A2,B2,“Y”) – this formula returns the difference in whole years between my two dates which is 11
- =DATEDIF(A3,B3,“M”) – this returns the difference in whole months, so the value returned in 132
- =DATEDIF(A4,B4,“D”) – this counts the number of days between your two dates, so it returns 4019
- =DATEDIF(A5,B5,“MD”) – this counts the number of days after subtracting months, so it only returns a count of 1 since there is a difference of one day after disregarding the months
- =DATEDIF(A6,B6,“YM”) – this counts the number of whole months between my two dates, after subtracting whole years. There is not a full month between the dates so it returns a zero
- =DATEDIF(A7,B7,“YD”) – this counts the number of days between the dates assuming they are not over a year apart. So since they are only 1 day apart it returns 1
If you are regularly working with dates in your spreadsheet and need to find out the difference between two dates, this function is incredibly useful.
It’s pretty easy to use. The hardest part is getting comfortable with the ways that the “unit” parameter affects the count, so make sure to take some time learning this so that you fully understand it.