Automate Daylight Savings Time formula

  • 0
  • 1
  • 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)),
Photo of bmackey

bmackey

  • 20 Points

Posted 2 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,372 Points 20k badge 2x thumb
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) {

});
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
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)