profile

Google Sheets Tips

Sheets Tip 204: How To Use Dates In The QUERY Function

Published almost 2 years ago • 1 min read


Hi Reader,

Welcome to the Google Sheets Tips newsletter #204!

➜ News

I. The QUERY Function Course
In case you missed the announcement earlier today, my newest course is now open for enrollment! Find out more:

The QUERY Function in Google Sheets

(Enjoy 50% off this week, until Friday at midnight EDT.)

_______

➜ Google Sheets Tip #204: How To Use Dates In The QUERY Function

Here is your Google Sheets tip, which you can think of as your Monday morning espresso, in spreadsheet form.

Working with dates in the QUERY function is tricky.

With today's tip, I want to show you how to reference dates correctly in the QUERY function.

Attempt 1: Try A Regular Date ❌

If you simply drop a date into your QUERY function, for example as a condition in the WHERE filter, it won't return the data you're looking for.

This formula does NOT return any data:

=QUERY( A1:E100 , "select * where B > '5/16/2022' ")

Attempt 2: Try A Formatted Date ❌

The QUERY function needs dates in the ISO format, which is "YYYY-MM-DD".

That's a 4-digit year, a dash, two digits for the month, a dash, and finally, two digits for the day.

For example, today's date is written 2022-05-16.

Putting that into the formula looks like this:

=QUERY( A1:E100 , "select * where B > '2022-05-16' ")

Unfortunately, it still doesn't return any results. Hmm, there's one more thing we need to do...

Attempt 3: Add the "date" Keyword ✅

The last piece of the puzzle is to add the "date" keyword before the date in your filter condition, like this:

=QUERY( A1:E100 , "select * where B > date '2022-05-16' ")

Woohoo!

Now the QUERY function will parse the data correctly and return all data after today's date.

Read more about filtering dates with the QUERY function.

Or, dive deep into the QUERY function (including dates!) in my new course:

The QUERY Function in Google Sheets

_______

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

Google Sheets Tips

by Ben Collins

Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.

Read more from Google Sheets Tips

Hi Reader, Welcome to the Google Sheets Tips newsletter #296, your Monday morning espresso, in spreadsheet form! A big thank you to this week's sponsors who help keep this newsletter free to read: TAPLIO Grow your LinkedIn audience with the power of AI Taplio helps you grow and leverage your personal brand on LinkedIn to build an audience and attract more leads and opportunities. Create high-performing LinkedIn posts, engage with your target audience, find leads and measure all your results....

4 days ago • 2 min read

Hi Reader, Welcome to the Google Sheets Tips newsletter #295, your Monday morning espresso, in spreadsheet form! A big thank you to this week's sponsors who help keep this newsletter free to read: SHEET CHAT Your Powerful AI Google Sheets Copilot Sheet Chat is an AI-powered Copilot that helps you work efficiently in Google Sheets and Google Docs. Use it for free with your OpenAI API key and hosting, and create your own custom agent based on your specific requirements. Try it for free!...

11 days ago • 3 min read

Hi Reader, Welcome to the Google Sheets Tips newsletter #294, your Monday morning espresso, in spreadsheet form! A big thank you to this week's sponsors who help keep this newsletter free to read: SAPIENGRAPH Add LinkedIn and Crunchbase data via custom formulas to GSheets Sapiengraph makes it easy to fill your Google Sheets with fresh, accurate data from LinkedIn and Crunchbase. Using our intuitive custom formulas: VCs can track the employee count of portfolio companies with...

18 days ago • 2 min read
Share this post