We all know that a year has 365 days, a month 30, and a week 7. But unlike keeping track of years and months, calculating the number of days elapsed between two dates is more challenging than you’d think. However, Google Sheets allows you to use a simple formula to conveniently find the difference between dates.
The DATEDIF function on Google Sheets takes two dates and works out the difference in years, months, and days. Moreover, the syntax is quite simple, and you don’t have to reach for a pen and paper or a calculator while working on your Sheets. But if this is your first time running into the DATEDIF function, don’t worry.
This post will explore how to calculate days between dates in Google Sheets and provide examples you can follow. Using this method, you can also calculate months and years between dates.
Keep reading to learn more.
What is the DATEDIF Function on Google Sheets
Before we get into what DATEDIF means, let’s first start with the proper pronunciation of the word. You should pronounce it as DATE DIF and not as “DETED IF.” DATEDIF stands for date difference, which, as the name suggests, calculates the difference between two dates.
The DATEDIF syntax is as shown, and has three arguments: =DATEDIF(start_date, closed_date, unit).
- Start_date- this is the starting point for the formula and must be represented in one of the following ways:
- A date: 9/26/2016
- A cell number representing a date: A2
- A formula representing a date: DATE(9/26/2016)
- End_date- this is the calculation’s endpoint. It should be represented in the formula, like the start date.
- Unit- this delineates what difference the formula should return. Here is a list of the units you should expect:
- “D”: means days and returns the difference between two dates in total days.
- “M”: means months and returns the difference in months between two dates.
- “Y”: means year and returns the difference between two dates in years.
- “MD”: means days ignoring months and returns the number of days left after subtracting full months.
- “YD”: means days ignoring years and returns the number of days left after subtracting full years from two dates.
- “YM”: means months ignoring years and returns the number of months left after subtracting full years from two dates.
You should note that they all appear with double quotation marks and should be included when writing your formula. Now let’s put everything together using an example.
How to calculate days between dates in Google Sheets
In our Google Sheets example, we have two dates defined and want to get the number of years, months, and days between them.
Here is what our Google Sheets look like:
Step 1: Pick a Cell
We will start by calculating the number of days between the two dates. This means we will use the E1 cell to the right side of the number of days entry on our table.
Step 2: Input the Formula
With the cell where we want our output generated selected, head over to the formula bar and input the following password:
=DATEDIF(B1,B2,”D”)
Step 3: Hit Enter
When you hit enter, the formula should return 611 as the result. This is the number of days between the opening and the closing dates.
Step 4: Pick the Month Cell
Next, you want to calculate the number of months. To do this, selected cell E2 on the right side of the number of months entry on our table and input this formula:
=DATEDIF(B1,B2,”m”)
It should look something like this:
When you press enter, you should get 20 months as the result.
Step 5: Pick the Year Cell
As with the first and the second cells, you will pick cell E3 to the right side of the number of years entry on our table. Then input this formula:
=DATEDIF(B1,B2,”Y”)
If you did everything right, you should get 1 as your result.
Here is how your result column should look after you’ve input all the formulas.
However, that’s not all. We will now calculate the number of full years, months, and days felt after subtracting each from our total number of days.
Calculating the Number of Full Years, Months, and Days Between the Opening and Closing Dates
This DATEDIF function makes it easy to visualize the difference between the opening and closing dates in a more clustered manner. You should also note that the formula to calculate the number of years will remain the same, and as such, we will not repeat it again for this post. Just keep in mind that in our example, we only have 1 full year.
Here are the steps to determine the remaining months and days.
Step 1. Pick a Cell
Looking at our table, you will also see a years, months, and days section under columns D, E, and F. Pick the cell below the months’ entry on our table.
Step 2. Input Formula
To calculate the number of months left after subtracting years from our total days, input this formula:
=DATEDIF(B1,B2,”YM”)
Step 3. Enter
After hitting enter, you should get 8 months as the result.
Step 4. Pick the Days Cell
Pick the cell below the days’ entry on our table and input this formula:
=DATEDIF(B1,B2,”MD”)
When you hit enter, you should get 4 days as the result.
Your completed table should now look like this.
As you can see, calculating the difference between dates on Google Sheets is relatively easy using the DATEDIF function.
Conclusion
Working on Google Sheets is something most of us have to do on a frequent basis. Moreover, there is no telling what kind of data you will handle or your next challenge. However, it’s always comforting to know that Google Sheets offers convenient ways to analyze and sort data.
DATEDIF is one excellent example that will come in handy when dealing with multiple dates. As we’ve seen, you can now confidently calculate days between dates in Google Sheets and also determine the total months and years between them. Furthermore, you can also use the DATEDIF function to analyze the dates further and even calculate how long the difference is in specific years, months, and days.
We hope you find this post helpful and find it easier to calculate your days, months, and years on Google Sheets.