How to Make Date Functions Aware of Daylight Savings Time (DST)

Gido

Goal

When I make a list of dates using EXPAND_DATE_RANGE

=EXPAND_DATE_RANGE(TODAY()-5000d;TODAY();1d)

sometimes it will generate the values at 12AM (as the GROUPBY function will) and sometimes, it will generate them at 11PM.

The reason?

This is attributed to Daylight Savings Time (DST) and the way time is calculated in Datameer.

Learn

To make a function aware of Daylight Savings Time (DST) use ADDTODATE in order to add or subtract time from a date.

=EXPAND_DATE_RANGE(ADDTODATE(TODAY();"-5000d");TODAY();1d)

This is also recommended if you are working with Partitioned Data and Advanced Partition Filter.

E.g., In order to catch the last seven days of the previous month in a workbook using a formula that respects daylight saving time.

$partition < ENDOFMONTH(STARTOFMONTH(ADDTODATE(TODAY();"-1M"))) && $partition >= ENDOFMONTH(STARTOFMONTH(ADDTODATE(TODAY();"-1M")))-7d