Datedif in Google Sheets

What is Datedif in Google Sheets?

The DATEDIF in Google Sheets calculates the count of the days, months, or years between two dates. The function returns the difference in value between two dates, either as days, months, or years. The function can also return months and days difference while ignoring the year difference. In this article, we will discuss in detail about this function. Before that, let us look at a simple example of how the DATEDIF in Google Sheets works.

Let us count the number of days a movie ran in a movie theatre complex. Let us enter the start and the end date and calculate how much time it’s going to take to complete it in days, months, and years. Apply the formula as shown below in cell B3.

=DATEDIF(B1,B2,”D”)

Here, we give the start date and end date as arguments, besides “D,” indicating the number of days. You can see the result of the function, that is, the number of days the movie ran is 48.

What is Datedif in Google Sheets?

DATEDIF in Google Sheets is used to calculate the difference between two dates. The output of this function can be in days, months, or years.
The syntax of the function is as follows: =DATEDIF(start_date, end_date, unit)
- start_date: The starting date
- end_date: The ending date
- unit: The difference in days, months, or years.
The function handles leap years accurately.
The dates should be in a recognizable date format and should be converted to the date format if they are in the text format.
It has various uses, such as age calculation, tenure calculation, etc.

Datedif() Google Sheets Formula

The syntax of the DATEDIF formula in Google Sheets is as follows:

=DATEDIF(start_date, end_date, unit)

start_date: The beginning date for the calculation.
end_date: The ending date for the calculation.
unit: The unit of measurement. It can be any of the following.

“D” – returns the number of days between two dates.

“M” – the number of months between two dates(only full months).

“Y” – the number of years(only full years).

“MD” – the number of days ignoring months.

“YD” – the number of days ignoring years

“YM” – the number of complete months after subtracting full years

How to Use Datedif Google Sheets Function?

To use the DATEDIF function in Google Sheets, follow these steps:

Step 1: Let us look at a simple example where we enter two different dates to find the difference between them in terms of days, months, and years.

Step 2: Click on the cell where you want to display the difference between the dates. Enter the formula manually as follows.

=DATEDIF(

Step 3: Type out the arguments. Here, the syntax of DATEDIF in Google Sheets is:

=DATEDIF(start_date, end_date, unit)

Type =DATEDIF(B1, B2, “D”)

Step 4: Press Enter. You get the number of days between the two dates.

Step 5: We can find the number of months between them with the following formula.

=DATEDIF(B1,B2,”M”). Press Enter.

Using the Google Menubar

Choose the cell where you want to enter the formula.
Go to the menu bar and click on “Insert” ➝ “Function” ➝ “Date” ➝ “DATEDIF“
Enter the start date, end date, and the unit as arguments. Close the bracket and press the “Enter” key.

Examples

The DATEDIF in Google Sheets is versatile and can be used in a number of different ways when it comes to calculations involving dates. Let us look at some interesting examples below.

Example #1 – Number of Days Between the Two Dates

In the spreadsheet below, we have the date of joining of an employee, John. Here, we can calculate how many days have passed since he joined the organization and the number of days left until his first-year completion. We have calculated this as follows.

Select the cell where you have input the first date, which is B1.

Step 1: To input the current date, we use the Datedif in Google Sheets TODAY function. It helps us calculate the difference between today’s and John’s date of joining using the DATEDIF function.

Step 2: Select the cell where you want to show the current date, which is B2. After you type =TODAY(), press Enter.

This will get today’s date and display it in the cell. Remember, the returned value is in the date format.

Step 3: Now, let us calculate the days that have passed since John’s date of joining with DATEDIF.

Select the cell where you want to show the results. Enter the formula shown below.

=DATEDIF(B1, B2, “D”)

Step 4: Press Enter. It will calculate the difference between the dates B1 and B2. The third parameter, D, instructs the function to find the difference in days. The result shows that the number of days since John joined the organization was 203.

Example #2 – Count the Number of Days Ignoring Years

Surprisingly, DATEDIF can also calculate the number of days between two days, ignoring the years. For example, we have Gia’s date of birth and the current date. You can find how many days to her birthday, ignoring the years, using DATEDIF and a suitable parameter.

Step 1: Let us enter the dates in a Google sheet. Use the TODAY() function to get today’s date.

Step 2: Now, let us find the number of days to her birthday. Here, we ignore the years between the two dates and find the number of days between them. Hence, we use the third parameter as “YD.”

Type the following formula in cell B3.

=DATEDIF(B1, B2, “YD”)

Step 3: Press Enter. You get the number of days between the two days, ignoring the years.

Example #3 – Count the Number of Months Between the Two Dates

Now, we can look at how to use the same DATEDIF function to count the number of months between two dates. A town that experiences very little rainfall entered the two dates when they last had rainfall. Let us calculate how many months it took for the next rainfall to occur.

Step 1: Enter the two dates in your spreadsheet.

Step 2: To find the number of months between them, enter the following formula in cell B3.

=DATEDIF(B1,B2,”M”)

Step 3: Press Enter. You get the number of months between the two rains.

Example #4 – Find the Days Excluding Years and Months

In this example, we keep it simple to clearly understand how to find the number of days, ignoring months and years in Google sheets. Let us suppose we have two dates.

Step 1: To find the number of days between the two dates, ignoring the months and even years, we use the following formula in cell B3.

Step 2: Press Enter. You get the number of days between the two dates.

Datedif Function not Working

The DATEDIF function deals with the DATE format, which may be confusing at times. You may encounter the following DATEDIF in Google Sheets errors.

#VALUE! error – It occurs when the formatted dates are incorrect. Should you get this error, check the date formats for accuracy. Sometimes, the date may be in the text format or a wrong format leading to this error.
#NUM! error – If the ‘unit’ parameter is invalid, you get this error. Check for suitable options before entering it.
#NAME? error – It arises due to the function being misspelled.

Important Things to Note

If the DATEDIF in Google Sheets is to be copy-pasted and you are using the exact dates in different cells, use the cell references to avoid errors or incorrect results.
You have a helpful function besides DATEDIF to calculate the number of working days, excluding weekends. It is called NETWORKDAYS. It calculates the number of working days between two dates with the exclusion of Saturdays and Sundays and even holidays.
Ensure that the start date and end date are in the accurate date format to avoid unnecessary errors when using DATEDIF in Google Sheets.
Suppose you have a list of dates in two columns, and you have to calculate the number of days between them. Instead of using the DATEDIF by copy-pasting, you can use the array formula function. For example,

=ArrayFormula(DATEDIF(A2:A10, B2:B10, “D”)).

Frequently Asked Questions (FAQs)

What are the limitations of DATEDIF in Google Sheets?

The DATEDIF function has the following limitation: it operates within the 1900 date system. Hence, it relies on dates to be serial numbers. Besides, you have to ensure that the dates are entered in the correct format.

Is the DATEDIF in Google Sheets case-sensitive, and does it consider leap years?

No, the DATEDIF function is not case-sensitive. The parameters can be either in caps or small letters and will be interpreted accurately. (e.g., “m” or “M” for months). The DATEDIF function does accurate calculations for leap years when calculating differences between dates, ensuring the results are right even across leap years.

What are some of the common errors associated with the DATEDIF function?

DATEDIF will throw a #NUM error if the start_date is greater than the end_date. Also, if the ‘unit’ parameter is invalid, you get this error.
You get the #VALUE error when the date format is not used or is inaccurate.

Datedif in Google Sheets