Google sheets Dynamic Start and End Dates Google Sheets

Google sheets Dynamic Start and End Dates Google Sheets,google-sheets,google-analytics,formula,google-sheets-formula,Google Sheets,Google Analytics,Formula,Google Sheets Formula,I'm trying to create a dynamic dashboard in google sheets with the google analytics add-on. I'm having trouble figuring out how to make the start and end dates dynamic. I would like to look at Start Date: Past 14 days End Date: yesterday and then also run the same report for Start Date: Past 14 days last year End Date: yesterday last year how can I come up with these start dates?

I'm trying to create a dynamic dashboard in google sheets with the google analytics add-on. I'm having trouble figuring out how to make the start and end dates dynamic. I would like to look at

  • Start Date: Past 14 days
  • End Date: yesterday

and then also run the same report for

  • Start Date: Past 14 days last year
  • End Date: yesterday last year

how can I come up with these start dates?


#1

You can use simply TODAY fomula and subtract the number of days you want.

In the case of your example:

=TODAY()-14
Start Date: 28/12/2019

=TODAY()-1
End Date: 10/01/2020


=TODAY()-379
Start Date: 28/12/2018

=TODAY()-366
End Date: 10/01/2019

#2

Use query, get yesterday now-1 and operate it with the year - 0 for this year and year-1 for last year

#3

I agree with Past 14 days and Yesterday, but for same stuff for last year I would use EDATE(). Like: EDATE(TODAY()-14,-12) and EDATE(TODAY()-1,-12)