Syntax=DATEDIF(start_date, end_date, unit)start_date: Contains the start date, should be a reference to a cell containing a DATE, a function returning a DATE type, or a number.end_date: Contains the end date, should be a reference to a cell containing a DATE, a function returning a DATE type, or a number.unit: Contains the option to change unit of time. The available options are as follows:
“Y”: whole years between start_date and end_date
“M”: whole months between start_date and end_date
“D”: days between start_date and end_date
“MD”: days between start_date and end_date after subtracting whole months
“YM”: months between start_date and end_date after subtracting whole years.
“YD”: the number of days between start_date and end_date, after subtracting the number of whole years
Sample Usage=DATEDIF(DATE(2022, 1, 1), DATE(2022, 9, 30), "D")
//This returns the days between Jan 1 2022 and Sep 30 2022, which is 272.
Note: The first parameter must be earlier than the second parameter. Otherwise, the function returns a #NUM! error.
We will look at a function DATEDIF in Google Sheets and will look at how to find the number of Years, Months and Days between two dates in Google Sheets.
There can be many instances where finding days between two dates in Google Sheets can be useful. For example, if you want to find the number of days between the value of a stock at two instances. To achieve this, we will be using DATEDIF function. This function not only returns the days between two dates in Google Sheets but also gives you the options to return years, months and many more attributes. We will be discussing this in detail in next section.
Sample Usage
=DATEDIF(DATE(2021, 3, 11), DATE(2021, 9, 4), “D”)
=DATEDIF(B6, B7, “YM”)
=DATEDIF(“9/16/2008”, “10/24/2010”, “Y”)
Usage Example
Below is a sample sheet that we used to showcase the different attributes that we can use.
One thing to keep in mind is that the DATE function prints the date in MM/DD/YYYY format.
“Y”, “M” and “D” are easy to understand so let’s go through “MD”, “YM” and “YD”.
- MD is the number of days between start and finish after subtracting the whole year. So if the start date is 9/4/2014 and the end date is 10/11/2021 (Remember the date are in MM//DD//YYYY format), the difference between the days is 7 (11-4).
- Similarly, YM is the number of whole months after subtracting the number of whole years. For example, between 9/4/2014 and 10/11/2021, the difference is 1(10-9).
- YD is the number of days after subtracting the number of years. For example, between 9/4/2014 and 10/11/2021 there are 37 days if we exclude the whole years.
Conclusion
We had a look at DATEDIF functions and explored the different attributes associated with the function. Using the function, we saw how to find the number of years, months and days between two dates in Google Sheets. You can read more about DATEDIF in the official documentation here.
See Also
If you want to know more formulas and functions in Google Sheets, look at our definitive guide on Google Sheets which covers hundreds of such topics here. Enjoy reading!
Introduction to DATE function in Google Sheets: We will have a look at the DATE Function in Google Sheets and see how to reference the month, day and year from another cell.
Tips & Tricks: Working with Date and Time in Google Sheets: We will look at some commonly used tips, tricks and functions related to date and time in Google Sheets.