# Automate Daylight Savings Time formula

• 0
• Question
• Updated 2 years ago
• Acknowledged
• (Edited)
We have a 'Client's Local Time' field which we currently swap formulas in twice a year. We're located in AZ so we don't have a time change but need to correctly indicate the client's time. I'd like to automate the change but am having a hard time coming up with a solution that doesn't include hardcoded dates.

Below are examples of the code. Thanks in advance!

// DST
Case([State],
"AL",ToTimeOfDay(Now()+Hours(1)),
"AK",ToTimeOfDay(Now()+Hours(-2)),
"AZ",ToTimeOfDay(Now()+Hours(0)),

// ST

//Case([State],
//"AL",ToTimeOfDay(Now()+Hours(2)),
//"AK",ToTimeOfDay(Now()+Hours(-1)),
//"AZ",ToTimeOfDay(Now()+Hours(0)),
• 20 Points

Posted 2 years ago

• 0
• 31,698 Points
According to Wikipedia "Daylight saving time starts on the second Sunday in March and ends on the first Sunday in November, with the time changes taking place at 2:00 a.m. local time."  https://en.wikipedia.org/wiki/Daylight_saving_time_in_the_United_States

So if you know that, you can choose which formula to use.

You'll need to evaluate what today is in relation to those dates.

March Date:
Date(Year(Today()), 3, Case(DayOfWeek(Date(Year(Today()), 3, 1)),
0, 8,
1, 14,
2, 13,
3, 12,
4, 11,
5, 10,
6, 9,
null))

I'm going to have to circle back to this, but you need to evaluate the when 'today' in in relation to the switch date.

I'll answer this completely later tonight.

p.s. You can do this with Java automatically, if you want a script to run on your app.

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,224 Points
Use script along with the library moment.js and moment-timezone.js

http://momentjs.com/timezone/

QuickBase uses moment.js and you can include it with a require statement:

require(["moment"], function(moment) {

});
• 31,698 Points
If you don't know how to make the Java run or use the code pages, you can use this formula:
Just Copy this into a formula text field called something like 'My Daylight Savings Season Field'

```//variables to set the dates in relation to today's date
var text CurrentYearMarch=ToText(Date(Year(Today()), 3, Case(DayOfWeek(Date(Year(Today()), 3, 1)), 0, 8, 1, 14, 2, 13, 3, 12, 4, 11, 5, 10, 6, 9, null)));

var text NextYearMarch=ToText(Date(Year(AdjustYear(Today(),1)), 3, Case(DayOfWeek(Date(Year(AdjustYear(Today(),1)), 3, 1)), 0, 8, 1, 14, 2, 13, 3, 12, 4, 11, 5, 10, 6, 9, null)));

var text CurrentYearNovember=ToText(Date(Year(Today()), 11, Case(DayOfWeek(Date(Year(Today()), 11, 1)), 0, 1, 1, 7, 2, 6, 3, 5, 4, 4, 5, 3, 6, 2, null)));

var text LastYearNovember=ToText(Date(Year(AdjustYear(Today(),-1)), 11, Case(DayOfWeek(Date(Year(AdjustYear(Today(),-1)), 11, 1)), 0, 1, 1, 7, 2, 6, 3, 5, 4, 4, 5, 3, 6, 2, null)));

//formula to evaluate the current season
If(Today()<ToDate(\$CurrentYearMarch) and Today()>ToDate(\$LastYearNovember), "Winter",

If(Today()>=ToDate(\$CurrentYearMarch) and Today()<ToDate(\$CurrentYearNovember), "Summer",

If(Today()>=ToDate(\$CurrentYearNovember) and Today()<ToDate(\$NextYearMarch), "Winter", "ERROR")))

//Then use the "Winter" or "Summer" result to determine your formula to use.
// Something along the lines of
// If([My Daylight Savings Season Field]="Winter", [my winter formula], [my summer formula])```
(Edited)