Custom dynamic date range in Google Sheets

There are two ways to insert a custom date range to your query, in case you can't seem to find a suitable date in the Supermetrics sidebar "Select dates" dropdown, and a static date is not an option.

The Start and End Date fields accept relative date formats and a cell reference to a cell containing a date.

1. Relative date format, or the written format of a date

This is based on relative date formats of the PHP coding language. Anything that PHP's strtotime function understands can be put into the Start and End Date fields.

Using this format, it is easy to create dynamic date ranges, to keep your query up to date when you do a refresh.

The easiest and most used written date formats are:



-x days, replacing x with the amount of days

It is also possible to set up more complex dynamic date ranges. Examples:

"I use automated refresh daily, and need the queries to get data from 2019-02-01 until yesterday's date."

"I need to fetch the data from 1st of January last year, until today's date last year."

"We report on 3 week period, and the report needs to always start on Monday 3 weeks ago and keep going until today."

2. Cell references

In this method you simply need to provide a cell reference in the date field, for example "=A1" without the quotes.


"I wish to use the Refresh Active Sheet function in Supermetrics menu to update the query, but I also need to adjust the query dates sometime."

"The report needs to keep going from the 15th of last month until the 15th of the current month."

Please note! The example formulas in this article are done with European spreadsheet formula separator ";". If you use Google Sheets in the US, the separator inside the formula needs to be ",".

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.