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?
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).
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
In cell B2, to get the 3rd Sunday in March for that year
In cell C2, to get the last Sunday in October for that year
In cell D2, to get the first Sunday in November for that year
In cell G2, to convert to Central time allowing for daylight saving
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.