Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Daylight Savings Time Funcitions

This is a discussion on Daylight Savings Time Funcitions within the Excel Questions forums, part of the Question Forums category; Howdy - First Post. I have a need for a function or formula to determine if a date should have ...

  1. #1
    New Member
    Join Date
    Sep 2003
    Posts
    2

    Default Daylight Savings Time Funcitions

    Howdy - First Post.

    I have a need for a function or formula to determine if a date should have Daylight Saving Time (DST) applied. (e.g., Test(11/DD/YYYY)=Yes or =1)

    I understand the 'rules to be': for areas of the country who observe DST, DST occurs between the 4th Sunday in October to the 4th Sunday in the following April. (The time switch occurs at 2 AM.)

    I can obviously determine the applicable month ranges
    (e.g., =IF((MONTH(A143)>=10)+(MONTH(A143)<=4),"DST month","")

    For me the tricky part has been determining the applicable period corressponding to the 4th Sunday so that I can combine the tests.

    I can determine what is the 4th Sunday (a date each month). However, I haven't determined when a month's date is >= the 4th Sunday.

    I might be dense, but 'am having trouble.

    Any ideas anyone?

    Thanks!

    BTW - Just bought Mr. Excel on Excel. I'm fairly adept, but it is still worth the investment!

  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Daylight Savings Time Funcitions

    Hi there MainStreet:

    Welcome to MrExcel Board!

    Please look at the following simulation ...

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book4___Running: xl97 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    *MonthNumberFourthSundayOfTheMonthOfCurrentYear*
    2
    *101/26/03*
    3
    *404/27/03*
    4
    *1010/26/03*
    Sheet2*

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Would this do? If I have misunderstood your question -- my apologies!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,359

    Default Re: Daylight Savings Time Funcitions

    =--OR((MONTH(C2)<=4)*(C2<=DATE(YEAR(C2),4,7-WEEKDAY(DATE(YEAR(C2),4,1),2)+22)),(MONTH(C2)>=10)*(C2>=DATE(YEAR(C2),10,7-WEEKDAY(DATE(YEAR(C2),10,1),2)+22)))

    which results either in 1 or 0. C2 houses a date to test.

    You can format the formula cell as:

    [=0]"";[=1]"DST month"

  4. #4
    Board Regular Todd Bardoni's Avatar
    Join Date
    Aug 2002
    Location
    Rochester, MI USA
    Posts
    3,044

    Default Re: Daylight Savings Time Funcitions

    Yogi,
    I'm not very good wit DATE formulas yet...if its not too much trouble, could you give a quick rundown of how your formula works...I can't seem to grasp it.

    Thanks,
    Todd
    Todd


    "I'm a Data Anlyst(intermediate) and have no idea what it's all about."

  5. #5
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209

    Default

    Basically Yogi's formula is calculating on a base Week day
    default of 1 - 7 = Sunday - Saturday using american Dates
    (to change to English others =(1&"/"&G2)+0-WEEKDAY(1&"/"&G2)+29)

    4th sunday = 4 X 7 + Base Day = 1 +> +29

    (B4&"/"&1)+0 coerces the date serial number for the 1st of the Month of the current year (US)


    Adding them together yeilds 4th Sunday as a Serial number.

    Note: Only applicable for North America as daylight savings is different for different continents eg.

    ****** content=Excel.Sheet name=ProgId>****** content="Microsoft Excel 9" name=Generator>**
    ContinentCountryBeginning and ending days
    AfricaEgyptStart: Last Friday in April
    End: Last Thursday in September
    *NamibiaStart: First Sunday in September
    End: First Sunday in April
    AsiaMost states of the former USSRStart: Last Sunday in March
    End: Last Sunday in October
    *IraqStart: April 1
    End: October 1
    *Israel(Estimate, Israel decides the dates every year)
    Start: First Friday in April
    End: First Friday in September
    *Lebanon, KirgizstanStart: Last Sunday in March
    End: Last Sunday in October
    *MongoliaStart: Last Sunday in March
    End: Last Sunday in September
    *Palestine(Estimate)
    Start: First Friday on or after 5 April
    End: First Friday on or after 5 October
    *SyriaStart: April 1
    End: October 1
    *IranStart: the first day of Farvardin
    End: the first day of Mehr
    AustralasiaAustralia - South Australia, Victoria, Australian Capital Territory, New South Wales, Lord Howe IslandStart: Last Sunday in October
    (region that encompasses Australia, Tasmania, New Zealand, and other islands in the South Pacific)End: Last Sunday in March
    *Australia - TasmaniaStart: First Sunday in October
    End: Last Sunday in March
    *FijiStart: First Sunday in November
    End: Last Sunday in February
    *New Zealand, ChathamStart: First Sunday in October
    End: First Sunday on or after 5 March
    *TongaStart: First Saturday in October
    End: First Saturday on or after 15 April
    EuropeEuropean Union, RussiaStart: Last Sunday in March
    End: Last Sunday in October
    *GreenlandStart: First Sunday in April
    End: Last Sunday in October
    North AmericaCanada, United States, Mexico, St. Johns, Bahamas, Turks and CaicosStart: First Sunday in April
    End: Last Sunday in October
    *CubaStart: April 1
    End: Last Sunday in October
    South AmericaBrazilStart: First Sunday in October
    End: Last Sunday in February
    *ChileStart: First Sunday on or after 9 October
    End: First Sunday on or after 9 March
    *FalklandsStart: First Sunday on or after 8 September
    End: First Sunday on or after 6 April
    *ParaguayStart: First Sunday in October
    End: Last Saturday in February
    AntarcticaAntarctica(same as Chile)
    Kind Regards,
    Ivan F Moala From the City of Sails

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Daylight Savings Time Funcitions

    Hi Ivan:

    Thanks for great information beautifully presented.

    Hi Todd:

    I hope Ivan's explanation will do -- otherwise post back and then let us take it from there.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  7. #7
    New Member
    Join Date
    Sep 2003
    Posts
    2

    Default Re: Daylight Savings Time Funcitions

    Thanks everyone.

    A fantastic and 'Elegant' solution!

    I'll be using this in Excel for some trans-time zone coordination and will try and adopt it for MS Project as well.

    ---------------------
    Dennis

  8. #8
    New Member
    Join Date
    Apr 2010
    Posts
    1

    Talking Re: Daylight Savings Time Funcitions

    Quote Originally Posted by Aladin Akyurek View Post
    =--OR((MONTH(C2)<=4)*(C2<=DATE(YEAR(C2),4,7-WEEKDAY(DATE(YEAR(C2),4,1),2)+22)),(MONTH(C2)>=10)*(C2>=DATE(YEAR(C2),10,7-WEEKDAY(DATE(YEAR(C2),10,1),2)+22)))

    which results either in 1 or 0. C2 houses a date to test.

    You can format the formula cell as:

    [=0]"";[=1]"DST month"
    I know that this was posted AGES ago, but I hope that someone can help me, as I am still confused with the explanation of this function. Can someone provide a NEW formula for Daylight Savings in the US for the year 2010. Thanks

  9. #9
    New Member
    Join Date
    Apr 2010
    Posts
    2

    Default Re: Daylight Savings Time Funcitions

    Hi,
    All very interesting, but the formula of Yogi's doesn't seem to work for August 2010, it gives the 5th (and last Sunday).
    Very curious however what '(1&"/"&G2)' actually does, as the brackets are important.
    Hope there is a reply to cortiknee, as I am still looking for a valid formulae, that gives DST from the last Sunday in March to the last Sunday in October.
    Thanks
    Bob

  10. #10
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,956

    Default Re: Daylight Savings Time Funcitions

    If you have a date in A2 this formula will give TRUE if that date falls anywhere in the period from the 2nd Sunday in March to the day before the 1st Sunday in November (US daylight saving)

    =MATCH(A2,DATE(YEAR(A2),{1,3,11},{1,15,8})-WEEKDAY(DATE(YEAR(A2),{1,3,11},7)))=2

    That will work for any date.

    For UK BST (Last Sunday in March to the day before last Sunday in October) you can change to

    =MATCH(A2,DATE(YEAR(A2),{1,4,11},1)-WEEKDAY(DATE(YEAR(A2),{1,4,11},7)))=2

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com