Google Sheets Query offers various functions and operators for data manipulation, including the DATEDIFF function. In this article, we will explore how to use the DATEDIFF function effectively and provide real-life examples to illustrate its applications.
What is DATEDIFF?
The DATEDIFF function is a scalar function in Google Sheets Query that calculates the date difference between two dates or timestamp values. Unlike aggregation functions, the DATEDIFF function returns one value per row and does not aggregate data.
DATEDIFF Function in Google Sheets Query: Example
Let’s start by learning how to find the difference between two dates using the DATEDIFF function in Google Sheets Query. We will then delve into some real-life examples to demonstrate its practical use.
1. Find the Number of Days Between Two Dates in the Query
To find the number of days between two dates using the DATEDIFF function, use the following syntax: dateDiff(end date, start date). This scalar function can be applied to a date or DateTime column, and it returns integer values as it truncates time values before calculation.
Here’s an example:
Sample Data:
| Work Start Date | Work End Date |
|-----------------|----------------|
| 2022-01-01 | 2022-01-10 |
| 2022-02-15 | 2022-02-20 |
| 2022-03-01 | 2022-03-05 |
Formula (D1):
=QUERY(A1:B,"Select A,B,dateDiff(B,A)")
You can also use the simplified formula to return only the number of days:
=QUERY(A1:B,"Select dateDiff(B,A)")DATEDIFF in Query Vs DATEDIF Worksheet Function: Differences
It’s important to note that DATEDIF is a stand-alone worksheet function, while DATEDIFF is specifically designed for use within Query. Though the DATEDIF function is feature-rich, we will only focus on the differences in returning the difference in days between two dates or DateTime values.
Consider the following formula to use the DATEDIF worksheet function:
It’s like:
=ArrayFormula(datedif(A2:A5,B2:B5,"D"))
The syntax follows:
datedif(start date,end date,unit)The key difference lies in the syntax order. In DATEDIF, the start date comes before the end date. Additionally, if a start date greater than the end date is specified, the scalar function returns a negative number, while the worksheet function returns the #NUM error.
Real-life Use of the DATEDIFF Function in Google Sheets Query
Now, let’s explore two practical examples to showcase the applications of the DATEDIFF function.
1. Finding the Upcoming Birthdays Using a Query Formula
Consider the following sample data:
| Date of Birth | Name |
|---|---|
| 1980-01-30 | John |
| 1995-05-05 | Emily |
| 1985-12-25 | Michael |
To find upcoming birthdays within 30 days, use the following formula:
=ArrayFormula(QUERY({A2:B,{(date(year(today()),month(A2:A),day(A2:A)))}},"select Col1,Col2 where Col2<>'' and dateDiff(Col3,now())=30"))This formula will return the dates of birth and corresponding names of individuals whose birthdays are coming up within 30 days from today.
2. Extracting Expiring Contracts
To extract contracts expiring within a specific timeframe (e.g., one month, 30 days, or any other custom number of days), use the DATEDIFF function within the Query formula. Here’s an example:
=QUERY({A2:B},"select Col1,Col2 where Col2<>'' and dateDiff(Col1,now())=30")
You can modify the formula by changing the value in the `=30` part to set a different expiry threshold.Additional Tips
In the examples above, the NOW() function is used to return today’s date. However, if you want to reference the date from a specific cell (e.g., C1), use the following formula:
=QUERY({A2:B},"select Col1,Col2 where Col2<>'' and dateDiff(Col1,date '"&TEXT(C1,"yyyy-mm-dd")&"')=30")Related: How to Use Date Criteria in Query Function in Google Sheets.
That’s all about the use of the DATEDIFF function in Google Sheets Query. Now, you can leverage this powerful function to perform date calculations and streamline data analysis in your spreadsheets.
For more insights and tips on Google Sheets and other digital marketing tools, visit Crawlan.com.
