The arithmetic operators in Query won’t work in a date or time column. So to add or subtract n days to the dates in a column in Query, you can follow the below two methods.

Assume column B contains dates. I am going to add 30 days to the dates in this column.

Does this Query work?

=Query(A1:C,"Select B+30")

It won’t work! You will get a #VALUE! error saying “can’t perform the function sum on values that are not numbers“.

Let’s try to subtract 30 days with the dates in column B using another Query.

=Query(A1:C,"Select B-30")

Here also you will see a similar error, i.e., “can’t perform the function difference on values that are not numbers“.

Related: Group and Sum Time Duration Using Google Sheets Query.

Then how to add or subtract n days to the dates in a column in Query? Is there any equivalent aggregation function available in the SQL Query in Sheets?

No! There are no such functions available. You can solve this issue using a workaround as below.

Two Methods to Add or Subtract N Days to The Dates in a Column in Query

Let me start with adding dates using method 1:

Adding 180 Days to a Date Column in Query

Method 1:

I want to add 180 days to the dates in column B. Also I want to filter column A containing the string “Hosting”.

As you may know by now, the arithmetic operator in SQL Query in Sheets works in a numeric column.

Dates are actually numbers in Google Sheets. So the arithmetic operator ‘Addition’ will work if you convert the dates in column B to numbers aka date values. How?

Select the dates in column B and go to the menu Format and select Numbers > Numbers. Then use the below formula.

=Query(A1:B,"Select A,B,B+180 where A contains 'Hosting' label B+180 'Expiry'")

The Query is in cell D1. You must format the range E2:F4 to date from Format > Number > Date.

Method 2:

In the second method no need to format the dates in column B to number. That we can do within Query data as below.

=ArrayFormula(Query({A1:A,datevalue(B1:B)},"Select Col1,Col2,Col2+180 where Col1 contains 'Hosting' label Col2+180 'Expiry'"))

I have used the DATEVALUE function within Query data. Since the header row includes in the DATEVALUE formula, it would return #VALUE! error as below.

That you can solve using labeling in Query.

=ArrayFormula(Query({A1:A,datevalue(B1:B)},"Select Col1,Col2,Col2+180 where Col1 contains 'Hosting' label Col2+180 'Expiry',Col2 'Join Date'"))

Subtracting 30 Days from a Date Column in Query

I think you can guess the formula now! Simply change + operator in the formula to -. In the below examples, I am subtracting 30 days from the dates in column A.

If you use the method 1 above, first you need to change the date format to number. Once done, you can use this Query.

=Query(A1:A,"Select A, A-30")

The output in column C and D will be in numbers. Do format it to date.

If you follow method 2, use this formula. Here you only format the output columns to date.

=ArrayFormula(Query(datevalue(A1:A),"Select Col1, Col1-30 label Col1 'Date', Col1-30 'Date-30'"))

This way you can add or subtract n days to the dates in a column in Query in Sheets.