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.
| Today | =TODAY() |
| Yesterday | =TODAY()-1 |
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 |
Monthly reporting
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.
7 comments
Comment by Sarah Reilly posted on
Hey,
Thanks for the helpful tips. I have a date challenge that I am having trouble to resolve - I need to have month to date comparing, I.e.: Jan 1-10 to Feb 1-10 and so on, till the end of the month, and then when month ends to have a new month to month comparison, I.e.: Feb 1-10 to Mar 1-10. Is there any date formula to this type of comparing?
If anyone has an idea, I will be glad to hear one. 🙂
Thanks,
Sarah Reilly
Comment by John Wedderburn posted on
Nice - myself and colleague were looking for just how to get ISO week numbers and this worked like a charm. Thanks for sharing!
Comment by Ashraf Chohan posted on
Thanks Paul
Hope to do a post in the near future on time and date formatting in Google Sheets. Will include this useful tip.
Comment by paul_l_cook posted on
Another great post, thanks!
Not date related but a nice tip to get something like ga:avgSessionDuration into the hh:mm:ss time format is to:
-In a new cell use the reference =A1/86400 (where A1 is the ga:avgSessionDuration value).
-Then format the new cell into 'Time' (Format > Number > Time in GA Sheets).
Comment by Charles posted on
Can we use the same formulas in Excel?
Comment by Ashraf Chohan posted on
Hi Charles
Yes, the formulas will also work in Excel so long as you ensure the relevant cell is formatted to 'Date'.
Comment by Jamie P posted on
Really helpful post. Thanks