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!

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)?

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

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.

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...