## When Your Formula Doesn’t Work: Formula Parse Errors in Google Sheets

Whether you’re just starting out with Google Sheets or are a seasoned pro, sooner or later one of your formulas will give you a formula parse error message rather than the result you want.

It can be frustrating, especially if it’s a longer formula where the formula parse error may not be obvious.

In this post, I’ll explain what a Google Sheets formula parse error is, how to identify what’s causing the problem, and how to fix it.

## Understanding Average In Google Sheets With The World’s Richest Person

This is a story about a bar, 10 regular folks, and the world’s richest man, to explore different measures of average in Google Sheets.

Somewhere along the way, we’ll seek to demonstrate the robustness of the different average measures, but more on that in a minute.

I want you to picture your favourite bar or pub.

For me, it might be a pint of ale at The Dickens Inn, near the River Thames in London:

I should just finish this blog post here, and we could all spend the rest of the day in happy reverie, supping our favourite tipple.

Alas, that won’t do! We have work to do and things to learn, so let’s get started.

## Filtering With Dates In The QUERY Function

If you’ve ever tried to filter on a date column in the Query function in Google Sheets, then you know how tricky it can be.

In a nutshell, the problem occurs because dates in Google Sheets are actually stored as serial numbers, but the Query function requires a date as a string literal in the format yyyy-mm-dd, otherwise it can’t perform the comparison filter.

This post explores this issue in more detail and shows you how to filter with dates correctly in your Query formulas.

## The problem

You might for example try the following syntax:

`=QUERY(Data!\$A\$1:\$H\$136,"select C, B where B > '1/1/2000'",1)`

Unfortunately, the output of such a query is blank:

If instead we remove the single quotes from around the date and try again, we get a #VALUE! error because the Query formula can’t perform the comparison:

Alas, what are we to do!

Neither of these “standard” formats work, because the dates are not in the correct format for the Query function.

## Correct syntax for dates in the Query function

Per the Query Language documentation, we need to include the `date` keyword and ensure that the date is in the format `yyyy-mm-dd` to use a date as a filter in the WHERE clause of our Query function.

Putting aside the Query function for a moment, let’s consider that `"select..."` string.

The new syntax we want will look like this:

`date_column > date '2000-01-01'`

Our challenge is to create a text formula to create this syntax for us, inside our query function.

Dealing with the text function first, starting with our required date of 1/1/2000 and working outwards:

First, we convert it to a serial number format with the `DATEVALUE()` wrapper:

`=DATEVALUE("1/1/2000")`

The output of this formula is a number:

`36526`

Then the `TEXT()` function converts it to the required format for the Query formula by specifying a format of `"yyyy-mm-dd"`:

`=TEXT(DATEVALUE("1/1/2000"),"yyyy-mm-dd")`

The output of this formula is a date in the desired format:

`2000-01-01`

Next we add single quotes around the new date format, with the `"'"` syntax. Finally, we insert the word `date` into the query string, to give:

`="select C, B where B > date '"&TEXT(DATEVALUE("1/1/2000"),"yyyy-mm-dd")&"'"`

which gives or desired output:

`select C, B where B > date '2000-01-01'`

That’s the syntax challenge done!

We can now plop that string into the middle argument of our Query function as per usual, and it’ll do the trick for us.

In this case, I was using a table of Space Shuttle mission data from Wikipedia, which contains a column of launch dates.

I used the `IMPORTHTML()` function to import that table into my Google Sheet, into a tab called Data in the range `A1:H136`. There’s a link to this dataset and worksheet at the end of the post.

This Query formula returns all of the Space Shuttle missions after 1 January 2000:

`=QUERY(Data!\$A\$1:\$H\$136,"select C, B where B > date '"&TEXT(DATEVALUE("1/1/2000"),"yyyy-mm-dd")&"'",1)`

The output of our formula is now returning the correct, filtered data:

## Referencing a date in a cell

The formula is actually simpler in this case, as we don’t need the DATEVALUE function. Assuming we have a date in cell A1 that we want to use in our filter, then the formula becomes:

`=QUERY(Data!\$A\$1:\$H\$136,"select C, B where B > date '"&TEXT(A1,"yyyy-mm-dd")&"'",1)`

## Example showing filter between two dates

Again, it’s relatively simple to extend our formula by adding a second date clause after the `AND` keyword:

`=QUERY(Data!\$A\$1:\$H\$136,"select C, B where B > date '"&TEXT(A1,"yyyy-mm-dd")&"' and B <= date '"&TEXT(B1,"yyyy-mm-dd")&"'",1)`

## Using today’s date as a filter

Substitute the TODAY() function into our formula:

`=QUERY(Data!\$A\$1:\$H\$136,"select C, B where B > date '"&TEXT(TODAY(),"yyyy-mm-dd")&"'",1)`