Relative Date Filtering with Timezones

When you need to include timezones in your relative date calculations, the best way to do that is to use an equation to test your values, then filter on that column.

Here are some basic relative date filters that include timezone. Replace [DATE] with your column of dates, and replace ​"America/Los_Angeles" with the timezone you would like to test against. 

 

Today

DateDiff("day", ConvertTimezone(Now(), "America/Los_Angeles"), [DATE]) = 0

 

Yesterday

DateDiff("day", ConvertTimezone(Now(), "America/Los_Angeles"), [DATE]) = -1

 

Tomorrow

DateDiff("day", ConvertTimezone(Now(), "America/Los_Angeles"), [DATE]) = 1

 

Month to Date

DateDiff("day", DateTrunc("month", Now(), "America/Los_Angeles"), ConvertTimezone(Now(), "America/Los_Angeles")) >= DateDiff("day", [DATE], ConvertTimezone(Now(), "America/Los_Angeles")) and DateDiff("day", [DATE], ConvertTimezone(Now(), "America/Los_Angeles")) > 0

 

Previous Month to Date

DateDiff("day", DateAdd("month", -1, DateTrunc("month", Now(), "America/Los_Angeles")), ConvertTimezone(Now(), "America/Los_Angeles")) >= DateDiff("day", [DATE], ConvertTimezone(Now(), "America/Los_Angeles")) and DateDiff("day", [DATE], ConvertTimezone(Now(), "America/Los_Angeles")) > 0‍

 

Next 10 Days

DateDiff("day", ConvertTimezone(Now(), "America/Los_Angeles"), [DATE]) <= 10 and DateDiff("day", ConvertTimezone(Now(), "America/Los_Angeles"), [DATE]) >= 0

 

Previous 10 Days

DateDiff("day", ConvertTimezone(Now(), "America/Los_Angeles"), [DATE]) >= -10 and DateDiff("day", ConvertTimezone(Now(), "America/Los_Angeles"), [DATE]) < 0

 

Next 7 Days

DateDiff("day", ConvertTimezone(Now(), "America/Los_Angeles"), [DATE]) <= 7 and DateDiff("day", ConvertTimezone(Now(), "America/Los_Angeles"), [DATE]) >= 0

 

Previous 7 Days

DateDiff("day", ConvertTimezone(Now(), "America/Los_Angeles"), [DATE]) >= -7 and DateDiff("day", ConvertTimezone(Now(), "America/Los_Angeles"), [DATE]) < 0