I regularly use the Google Analytics add-on in Google Sheets for reporting data to stakeholders. It's a way to obtain data via the API and run queries which are not possible to do within the Google Analytics interface.
I find one of the benefits of using the add-on is the ability to set relative dates using formulas in Google Sheets. This is particularly useful when setting up a report that requires updating on a regular basis. By setting up relative date ranges and using the scheduling feature in the add-on, I can set a report to automatically refresh on a periodic basis.
Date functions in Google Sheets
In this post I’ll share with you the main relative date formulas I use in source cells:
Today and yesterday
Formulas to get a cell to display today’s date and yesterday’s.
Set the date to x number of days ago
Setting the date to a set number of days ago is achieved by adjusting the above formula.
|5 days ago including today||=TODAY()-5|
|5 days ago excluding today||=TODAY()-6|
Standard Google Analytics reporting week
By default, weekly reports in Google Analytics are set from a Sunday to a Saturday. These formulas work out the dates for Sunday and Saturday for the last complete week.
|Sunday of last complete week||=TODAY()-WEEKDAY(TODAY(),1)-6|
|Saturday of last complete week||=TODAY()-WEEKDAY(TODAY(),1)|
ISO reporting week
If you prefer reporting from a Monday to Sunday here are the formulas.
|Monday of last complete week||=TODAY()-WEEKDAY(TODAY(),1)-5|
|Sunday of last complete week||=TODAY()-WEEKDAY(TODAY(),1)+1|
These formulas can be used to get the dates for the current month, the previous month and the month prior to that.
|Start of this month||=EOMONTH(TODAY(),-1)+1|
|End of this month||=EOMONTH(TODAY(),0)|
|Start of the last month||=EOMONTH(TODAY(),-2)+1|
|End of the last month||=EOMONTH(TODAY(),-1)|
|Start of the month before last||=EOMONTH(TODAY(),-3)+1|
|End of the month before last||=EOMONTH(TODAY(),-2)|
The formulas can be adjusted to be set to 4 months before, 6 months before etc.
Comparing year on year data
When looking at last month’s data I usually compare it with the data from a year ago. These formulas automatically work out the start and end dates of the same monthly data a year ago.
|Start of the month a year ago||=EOMONTH(TODAY(),-14)+1|
|End of the month a year ago||=EOMONTH(TODAY(),-13)|
All of the above formulas with actual dates have been included in this google sheet. Make a copy if you’d like to edit it.
Over to you
If there are relative date functions that you use regularly that I’ve missed, leave a comment below. We’ll add common formulas to the google sheet.
Ashraf Chohan is a senior performance analyst in GDS.