Expand all | Collapse all

Calculate Daylight Savings time

  • 1.  Calculate Daylight Savings time

    Posted 03-09-2020 12:00
    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. 
    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
    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. 

    Raj Helaiya

  • 2.  RE: Calculate Daylight Savings time

    Posted 03-09-2020 13:50
    Edited by Austin K 03-09-2020 14:09
    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.

  • 3.  RE: Calculate Daylight Savings time

    Posted 03-09-2020 14:03
    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

  • 4.  RE: Calculate Daylight Savings time

    Posted 03-09-2020 14:18
    Edited by Austin K 03-09-2020 14:19
    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.

  • 5.  RE: Calculate Daylight Savings time

    Posted 03-09-2020 14:26
    Yeah, I guess I should be looking at resolving this for all years. Thanks for the quick update. If you can think of a better solution, do let me know.

    Raj Helaiya

  • 6.  RE: Calculate Daylight Savings time

    Posted 03-09-2020 17:14
    Edited by Austin K 03-09-2020 17:35
    This is a really tough one to crack but I feel like I am getting closer. I tried earlier but was not able to figure out the calculation for the first Sunday of November. Second Sunday of March is easy enough. Look at this post and go over the rules for daylight savings time it gives and that should help.


    You can try this formula. It isn't fully done but I feel like it is almost there. Let me know if you can improve it and it works for you. There may be parts that are not needed in here, just what I put together earlier before stopping. Feels strange giving out something that may or may not work and need to be fixed, but here it is.

    The biggest issue with this is possibly the first November Sunday. Although it does actually work at least for 2019 and tested it for this year in March. 

    var number myMonth = Month([Created or Now]);
    var number myDay = Day([Created or Now]);
    var number previousSunday = $myDay - DayOfWeek([Created or Now]);

    If($myMonth < 3 or $myMonth > 11, "No",
    $myMonth >= 3 and $myMonth <= 11,
    $myMonth = 3 and $previousSunday >= 8, "Yes",
    $myMonth = 11 and $myDay > 7, "No",
    $myMonth = 11 and $previousSunday <= 0, "Yes", "No"
    ), "No"

    My "Created or Now" field is this:

    IsNull([Date Created]), ToDate(Now()), ToDate([Date Created])

  • 7.  RE: Calculate Daylight Savings time

    Posted 30 days ago
    Thanks Austin, I will try this and let you know.

    Raj Helaiya

  • 8.  RE: Calculate Daylight Savings time

    Posted 03-09-2020 16:49

    Outstanding analysis.
    Well done.

    Don Larson
    Westlake OH