How to Use the DATEDIFF Function in Google Sheets QUERY

Looking for the regular worksheet function? Use DATEDIF (one F). Looking to calculate date differences inside a QUERY formula? Use the SQL function DATEDIFF (two Fs).

The DATEDIFF function in Google Sheets QUERY returns the number of days between two date or DateTime values. It is one of the QUERY scalar functions, meaning it returns one calculated value for each row rather than aggregating multiple rows.

Unlike the DATEDIF worksheet function, DATEDIFF always returns the difference in days and does not support units such as months or years.

In this tutorial, you’ll learn the DATEDIFF syntax, see examples, and understand how it differs from the DATEDIF worksheet function.

Table of Contents

Syntax

DATEDIFF(end_date, start_date)

end_date – The later date or DateTime.
start_date – The earlier date or DateTime.

You can use this QUERY scalar function with Date or DateTime values.

The function returns whole numbers because it truncates the time portion before calculating the difference.

Example

I have a few work start dates in column A and work end dates in column B.

Let’s see how to use the DATEDIFF function in Google Sheets QUERY to return the number of days between the work start and end dates in each row.

=QUERY(A1:B, "Select A, B, dateDiff(B, A)")

The third column in the result contains the number of days between the two dates for each row.

Example of the DATEDIFF function in a Google Sheets QUERY formula

If you only need the number of days, use:

=QUERY(A1:B, "Select dateDiff(B, A)")

To assign a custom heading to the calculated column, use the LABEL clause.

=QUERY(A1:B, "Select A, B, dateDiff(B, A) label dateDiff(B, A) ' No. Days'")

DATEDIFF (QUERY) vs. DATEDIF (Worksheet Function)

DATEDIF is a stand-alone worksheet function, whereas DATEDIFF is a scalar function used inside a QUERY statement.

The DATEDIF worksheet function provides several units such as years, months, and days. Here we’ll compare only the behavior when calculating the difference in days.

If you use the worksheet function in cell D2, the formula would be:

=ArrayFormula(IF(A2:A*B2:B, DATEDIF(A2:A, B2:B, "D"),))

Since this is an array formula, wrapping DATEDIF in an IF statement prevents unintended results for blank rows by calculating the difference only when both dates are present.

The syntax is:

DATEDIF(start_date, end_date, unit)

Unlike DATEDIFF, the start date comes first in the DATEDIF syntax. The "D" unit returns the difference in days.

Besides the syntax, the two functions also behave differently in certain situations.

Scenario	DATEDIF	DATEDIFF
Both cells empty	0	Empty
Start date > End date	#NUM!	Negative value
Start date empty	Treats the blank cell as 30-Dec-1899	Empty
End date empty	#NUM!	Empty

Notes

DATEDIFF is available only inside QUERY.
It returns whole days only.
The time portion of DateTime values is ignored.
Blank dates return blank results instead of errors.
The order of the arguments is end date first, unlike DATEDIF.

Conclusion

The DATEDIFF function is useful when you need to calculate the number of days directly inside a QUERY statement. If you need years, months, or more flexible interval calculations outside QUERY, use the DATEDIF worksheet function instead.

13 COMMENTS

John January 28, 2022 At 1:11 pm

This is more of a general question relating to DATEDIF.

I am calculating years, months, and days using the DATEDIF formula based on the total number of days.

Here is the formula with total days being in cell A1.

=DATEDIF(0,A1,"y")&" years – "&DATEDIF(0,A1,"ym")&" months – "&DATEDIF(0,A1,"md")&" days"

My question: Months in the DATEDIF formula are considered full or complete months. Does this mean in my case where there are only days to calculate and no start date or end date, that complete months would mean the average number of days in a calendar month which is something like 30.437.days?

I’ve looked all over the web to find an answer and can’t see one.

- Prashanth January 31, 2022 At 10:25 am
  
  Hi, John,
  
  Please analyze the below formulas (both are equal).
  
  Formula 1:
  
  =datedif(0,64,"D")
  
  Formula 2:
  
  =datedif(date(1899,12,30),date(1899,12,30)+64,"D")
  
  Try replacing “D” with “MD” in both the formulas. I hope that clarifies.
  
Ted Stapenhorst October 14, 2020 At 8:46 am

Hi Prashanth,

I have 4 columns:

Location | Tested | DateOfTest | EmployeeType
District Office | Yes | 10/01/2020 | Staff
Maintenance | Yes | 10/10/2020 | Staff

I took your Query, modified it, and put it in F2 or the E column is blank:

=ArrayFormula(QUERY({A2:D,{(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())))}},"SELECT Col1,Col3,Col4 WHERE Col4 is not null AND datediff(Col3,Col5)<=14"))

I'm trying to pull out the rows where Col3 <= 14 days from today. I'm getting a REF error. Any help would be appreciated.

- Prashanth October 16, 2020 At 12:22 pm
  
  Hi, Ted Stapenhorst,
  
  Try this Query;
  
  =ArrayFormula(QUERY({A2:D},"SELECT Col1,Col3,Col4 WHERE Col4 is not null and Col3<=date '"&TEXT(today()-14,"yyyy-mm-dd")&"'"))
  
  or the following Filter.
  
  =filter({A2:A,C2:D},C2:C<=today()-14)
  
Nessus October 3, 2019 At 12:04 pm

Great… thank you very much.

So, it’s not possible to add also a text value to the formatted cells?

- Prashanth October 3, 2019 At 12:51 pm
  
  Only two values like the text “Ended” in the highlighted cells and the text “Pending” in the cells which are blank (not highlighted).
  
  On my shared example Sheet, I have used the following formula in cell C3.
  
  =ArrayFormula(if($C$1:$AG$1>=AH3,"Ended",))
  
  which then copied to C5 and C7.
  
  - Nessus October 3, 2019 At 1:20 pm
    
    Yes, but what if there are data before the ENDED date cell ?… then the ArrayFormula will not expand.
    
    - Prashanth October 3, 2019 At 2:00 pm
      
      Array formula requires blank cells to work. If not, it will return the #REF error. That’s why I omitted the array formula in my first reply.
      
      - Nessus October 3, 2019 At 2:45 pm
        
        So there is no other way to do this?… some script maybe?
        
        On another issue. If the expiry date it’s not yet set, then show empty cells.
        
Nessus October 2, 2019 At 10:44 pm

Ok… this is my sample file…

– File copied and link removed by Admin –

I’ve edited one row manually to show you what I am trying to do.

Thanks

Nessus

- Prashanth October 3, 2019 At 11:21 am
  
  Hi, Nessus,
  
  On your Sheet, I have applied the required conditional formatting rule.
  
  Here is a copy of your Sheet. In this copy, the range to highlight is C3:AG7 based on the dates in row#1 and column AH.
  
  https://docs.google.com/spreadsheets/d/1ry_ZXw02dAEiDDTy6alhHO4ZO4fbQM-WVPpVe8fbcQY/copy
  
  Formula Rule: =and(C$1>=$AH3,datevalue($AH3)>=1)
  
  The locale of this Sheet is UK. Since your Sheets’ locale is Greece, there the ' in the formula is replaced by a ;.
  
  Best,
  
Nessus October 2, 2019 At 3:11 pm

Hello there,

I have a question about this.

I have a sheet in sort of calendar format but the dates are as headers in one row on the top, the dates of the week are below in another row and the data are entered in the third-row below.

Now, I want to highlight/format the cells in the row containing the data based on the date that will be specified in a separate cell that is in the same row at the end of it.

Basically it’s a planner.

Thanks

Nessus

- Prashanth October 2, 2019 At 8:25 pm
  
  Hi, Nessus,
  
  Please show me a demo Sheet.
  
  Best,