Dynamic Interval Google Analytics

Support
#1

Hello,

I want to setup "Last 7 days" Interval on the formula of Google Analytics but the dates are not dynamics. When I put last 7 days in the formula these dates are insert :
"2020-11-27";"2020-12-03"
But tomorrow dates will be the same.
How can I make a formula to get last 7 days dynamically ?

I tried to put 2 cells with TODAY() and TODAY()-7 but when I atteched these cells to many formula I don't know why but the cells with formula refresh in loop.

Thank you for your help.

#2

The formula =Today() is messy because Excel updates it continuously, hence the loop. I would avoid it to make sure the formula doesn't drain your available API credits.

I recommend using a simple VBA script to update cells with the date today and seven days ago. This script can be executed at Workbook startup using events.

#3

Can I have a exemple for the VBA script please ?
And ho did you do that ? (This script can be executed at Workbook startup using event)

Thank you

#4

You can place this code in ThisWorkbook in the VBA editor:

Sub Workbook_open()
    Worksheets("Sheet1").Range("B1").Value = Date
    Worksheets("Sheet1").Range("B2").Value = Date - 7
End Sub

bild
bild.png1102×349 41.3 KB

Adjust the sheet name and range references in the code. Then save the workbook as .xlsm format.