How do I reference dates in cells?

I use formulas in Google Sheets to dynamically generate all the dates I need to use for my queries, and I want to reference those cells instead of hard coding the dates or using SM's template date ranges (screenshot).

I'm running into two issues:

1) If I click inside the date field the date picker obfuscates the view of the cell. I have to copy and paste the formula in because I can't see inside the field once I click on it (screenshot)

2) The date references work fine when I initially run the query. But if I refresh the queries, all of my date ranges are replaced with gibberish (screenshot), which destroys my queries (screenshot).  

So =Dates!B16, for example, turns into %RANGE:'Solutions Landing Pages'!Dates!B16% and trashes my entire document.

But the documentation on your site is woefully lacking in what this reference should look like (screenshot). And, though your site references this document, there is 0 guidance on how to use it. For example, when you click inside one of the date fields, you see this, but the example you share ("first day of last month") is nowhere in that help doc. 

What I'm trying to pull in are two dynamic dates:

  • Start Date: First day of first full month two years ago
  • End date: last day of last month

So if I ran the query today (3/10/17), my start date would be 3/1/15 and my end date 2/28/17. 

  • Hi Annie!

    Is this issue still topical or was the fix my colleague sent you via Twitter sufficient?


    -Supermetrics Team

  • Can you detail the solution here, I don't want to go through your tweets from 2 years ago.

Login to post a comment