How do I set up an Excel workbook to convert GMT to EST?

[49 forever]

I'm trying to use Excel to make a converter between GMT and EST/EST DST.

 

[Ron Rosenfeld]

On Wed, 13 Feb 2008 10:59:05 -0800, 49 forever <49

I'm trying to use Excel to make a converter between GMT and EST/EST DST.

Something like:

=A1-5/24+IF(AND(A1>=DATE(YEAR(A1),3,15)-WEEKDAY(DATE(YEAR(A1),3,14))
+7/24,A1<DATE(YEAR(A1),11,8)-WEEKDAY(DATE(YEAR(A1),11,7))+7/24),1/24,0)

should convert the date/time in A1, in GMT to EST/EDT in accord with the
current rules (2nd Sunday in March 2AM / 1st Sunday in November 2 AM)
--ron

 

[49 forever]

On Wed, 13 Feb 2008 10:59:05 -0800, 49 forever <49



Something like:

=A1-5/24+IF(AND(A1>=DATE(YEAR(A1),3,15)-WEEKDAY(DATE(YEAR(A1),3,14))
+7/24,A1<DATE(YEAR(A1),11,8)-WEEKDAY(DATE(YEAR(A1),11,7))+7/24),1/24,0)

should convert the date/time in A1, in GMT to EST/EDT in accord with the
current rules (2nd Sunday in March 2AM / 1st Sunday in November 2 AM)
--ron

 

[49 forever]

Great, works well. Can I impose on you to do it in reverse? I just don't
have the skills in Excel to reverse it. Thanks again.

 

[Ron Rosenfeld]

Great, works well. Can I impose on you to do it in reverse? I just don't
have the skills in Excel to reverse it. Thanks again.

With EST/EDT in A1, given the current rules, then:

=A1+5/24+IF(AND(A1>=DATE(YEAR(A1),3,15)-WEEKDAY(DATE(YEAR(A1),3,14))
+2/24,A1<DATE(YEAR(A1),11,8)-WEEKDAY(DATE(YEAR(A1),11,7))+2/24),-1/24,0)

should probably work to return UTC.
--ron