Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2009
    Posts
    204

    Unanswered: Question about formula for time change

    I'm not so good with date function formulas...

    What I need is to convert German time to Central time. Most of the year it is 7 hours ahead, but between the last sunday of Oct and the first sunday of Nov, and the second and last weeks of March, they are 6 hours ahead. Can anyone assist me in getting a formula that will do this?

    Thanks!

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Chris,

    Please can you give the exact rules you want the formula to use?

    On the dates that the clocks change, what exact time does the change kick in? 2 am?
    but between the last sunday of Oct and the first sunday of Nov
    So does this translate to
    >= 2am on last Sunday of Oct; and
    < 2am on first Sunday of November
    ?

    and the second and last weeks of March
    Please can you define the second and last weeks of March (similar to above)?

  3. #3
    Join Date
    May 2009
    Posts
    258
    I just had to deal with a similar issue with Daylight Savings Time (DST). Chip Pearson has a nice little module you can use to get the correct GMT Offset based on whether DST is in effect in the local time zone:
    Time Zones And Daylight Savings Time

    There's also a function here that will tell you whether DST is in effect (currently it is, so the GMT Offset for Central Time is -5).

    Hope this helps,

    Ax

  4. #4
    Join Date
    Oct 2009
    Posts
    204
    Quote Originally Posted by Colin Legg View Post
    Hi Chris,

    Please can you give the exact rules you want the formula to use?

    On the dates that the clocks change, what exact time does the change kick in? 2 am?

    So does this translate to
    >= 2am on last Sunday of Oct; and
    < 2am on first Sunday of November
    ?


    Please can you define the second and last weeks of March (similar to above)?
    It looks like the time can be either 2 or 3am in Germany depending on the year so idk, we can just always call it 3am...that part is not critical since we have nothing going on on Sundays here.

    For the other part...
    Before the second sunday in march...7 hour time difference.
    After the second sunday in march....6 hour time difference.
    After the third sunday in march....7 hour time difference.

    Same deal with between the last Sunday in Oct and the first in Nov.

  5. #5
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Chris,

    These formulas can probably be improved but here is my attempt...

    Say your German date and time is in cell F2.

    In cell A2, to get the 2nd Sunday in March for that year
    =DATE(YEAR(F2),3,16-WEEKDAY(DATE(YEAR(F2),3,1)))+TIME(3,0,0)

    In cell B2, to get the 3rd Sunday in March for that year
    =A2+7

    In cell C2, to get the last Sunday in October for that year
    =INT(DATE(YEAR(F2),10,30)/7)*7+1+TIME(3,0,0)

    In cell D2, to get the first Sunday in November for that year
    =C2+7

    In cell G2, to convert to Central time allowing for daylight saving
    =F2+(7-(F2>=A2)*(F2<B2)-(F2>=C2)*(F2<D2))/24


    These formulas obey the rules you gave so, for example:
    04 Nov 2003 02:58:00 becomes 04 Nov 2003 09:58:00, with a seven hour difference, but
    04 Nov 2004 02:58:00 becomes 04 Nov 2004 08:58:00 with a six hour difference.


    Example attached.

    Hope that helps...
    Attached Files Attached Files
    Last edited by Colin Legg; 03-27-10 at 07:21.

Posting Permissions

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