Skip to main content
Question

Calculate Daylight Savings time

Hello, I have a formula below, which calculates daylight savings time automatically. 

It calculates if the current date falls between second sunday of march and first sunday of novemeber. 
Scenario:
A formula text field called "Is it daylight savings time"  contains below mentioned formula:
var date firstdayofmarch = Date(Year(ToDate([Created or Now])),3,1);

var date secondsunday=LastDayOfWeek((LastDayOfWeek($firstdayofmarch)+Days(1)))+Days(1);

var date firstdayofnovember = Date(Year(ToDate([Created or Now])),11,1);

var date firstsundayofnovember = LastDayOfWeek($firstdayofnovember)+Days(1);

If (ToDate([Created or Now]) >= $secondsunday and ToDate([Created or Now])< $firstsundayofnovember
,"Yes"
,"No"
)
This is resulting in a "No", can anyone let me know why is not calculating in the right way? 
Create or Now field returns the current date. 
#formula 


​

------------------------------
Raj Helaiya
------------------------------

  • AustinK
  • Registered
  • March 9, 2020
Testing on a record from 2019 it works fine.

Testing on a brand new record(not created yet, so using Now(), it fails. It thinks the second Sunday of march is 3/15 which is obviously wrong. I broke out your formula below in many fields to check and see what was calculating wrong.

Since the issues appears to be with the secondsunday field I then broke that out even further.


Secondsunday - 2
secondsunday = LastDayOfWeek($firstdayofmarch);

Secondsunday - 3
secondsunday = LastDayOfWeek($firstdayofmarch)+Days(1);

Secondsunday - 4
secondsunday = LastDayOfWeek((LastDayOfWeek($firstdayofmarch)+Days(1)));

Then of course Secondsunday - final is your original full formula for secondsunday.

To me it looks like it is calculating correctly at the Secondsunday - 3 point, so basically changing the secondsunday part to be "LastDayOfWeek($firstdayofmarch)+Days(1);" seems to have fixed it for me in 2020, however your 2019 records will not calculate properly with this formula(it shows secondsunday as being 03/03/2019, which is wrong, should be 03/10/2019 ). Possibly other years as well. There must be a good solution here but I'm not sure what to tell you other than this will let you calculate correctly this year.

Also just so you are aware your current formula is also calculating the first Sunday of November incorrectly for 2020 as well but I imagine it is correct for 2019.

I do not know what you use this for but from my research QuickBase handles daylight savings time pretty well. It will be able to handle showing the correct times when a change has occurred even if the transition to daylight savings time happened during the event it is tracking time for(like start date and end date.) Just make sure your app is set up in the proper time zone already.
    Terms & ConditionsAccessibility statement