https://dataingovernment.blog.gov.uk/2016/01/21/using-relative-date-formulas-in-google-sheets-to-query-google-analytics-data/

Using relative date formulas in Google Sheets to query Google Analytics data

Calendar dates
Image licence: Creative Commons Attribution -  Dafne Cholet (Flickr)

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.

Referencing cells with relative date formulas
Referencing cells with relative date formulas

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.

6 comments

  1. Comment by Jamie P posted on

    Really helpful post. Thanks

    Reply
  2. Comment by Charles posted on

    Can we use the same formulas in Excel?

    Reply
    • Replies to Charles>

      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'.

      Reply
  3. 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).

    Reply
  4. 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.

    Reply
  5. 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!

    Reply

Leave a comment

We only ask for your email address so we know you're a real person