Forum Discussion

RajHelaiya's avatar
RajHelaiya
Qrew Captain
6 years ago

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
------------------------------
Formulas and functions
  • AustinK's avatar
    AustinK
    Qrew Commander
    6 years ago
    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.
    • RajHelaiya's avatar
      RajHelaiya
      Qrew Captain
      6 years ago
      Thank you Austin for the reply, just to be sure, should the formula for second sunday of march should be changed as below: 

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

      Corrected formula: var date secondsunday=LastDayOfWeek((LastDayOfWeek($firstdayofmarch)+Days(1)));   ?????

      ------------------------------
      Raj Helaiya
      ------------------------------
      • AustinK's avatar
        AustinK
        Qrew Commander
        6 years ago
        var date secondsunday = LastDayOfWeek($firstdayofmarch)+Days(1);

        That made it calculate that date correctly for me in 2020 but it will no longer calculate correctly for 2019 or probably other years either. So if the record was created after 3/8/2020 it will state it is daylight savings time. If not, it says no. Again just to be sure you saw, your November Sunday is also calculating wrong for 2020.

        There may be another solution that will actually calculate for all/most years correctly. I think it is going to be tough to do though.
    • DonLarson's avatar
      DonLarson
      Qrew Elite
      6 years ago
      Austin,

      Outstanding analysis.
      Well done.

      ------------------------------
      Don Larson
      Paasporter
      Westlake OH
      ------------------------------