EST/DST Time

H

Himu

hi
i would like to make a cell that shows the time in EST
throughout the year. Note: i have normal time set in
my "Time And Date Property", which means it changes time
with daylight saving. Please help me find a soltion so
that i get EST time in that excel worksheet all thoughout
the year.
thank you.
 
P

Peo Sjoblom

What is your normal time, pacific?
If so use

=TEXT(NOW()+3/24,"hh:mm")

mountain

=TEXT(NOW()+2/24,"hh:mm")

central

=TEXT(NOW()+1/24,"hh:mm")

use n/24 depending on where you are
 
N

Norman Harker

Hi Himu!

I think I'm reading this differently from Peo.

What you are saying is that you are in the EST Zone and have system
settings that automatically changed to EST / DST last weekend.

You want EST all the time without the DST adjustment (if in being)

First you need to establish the algorithm used for deciding when to
change the clocks.

Here's a chart I got from:

http://geography.about.com/cs/daylightsavings/a/dst.htm

Year Spring Forward Fall Back
2004 2 a.m. April 4 2 a.m. Oct. 31
2005 2 a.m. April 3 2 a.m. Oct. 30
2006 2 a.m. April 2 2 a.m. Oct. 29
2007 2 a.m. April 1 2 a.m. Oct. 28
2008 2 a.m. April 6 2 a.m. Oct. 26
2009 2 a.m. April 5 2 a.m. Oct. 25


From this chart, it looks like the appropriate dates for US are the
first Sunday in April and last Sunday in October.

This gives me a formula as follows:

=IF(AND(DATE(YEAR(NOW()),4,8)-WEEKDAY(DATE(YEAR(NOW()),4,7))+"2:00:00">=D1,NOW()<=(DATE(YEAR(NOW()),11,8)-WEEKDAY(DATE(YEAR(NOW()),11,7))+"2:00:00")-7),NOW()-1/24,NOW())

In this formula:
=DATE(YEAR(NOW()),4,8)-WEEKDAY(DATE(YEAR(NOW()),4,7))+"2:00:00"
Returns 2:00 AM on the first Sunday in April of the current year

=(DATE(YEAR(NOW()),11,8)-WEEKDAY(DATE(YEAR(NOW()),11,7))+"2:00:00")-7
Returns 2:00 AM on last Sunday in October of the current year

If NOW() is between those dates then your system clock has advanced an
hour and you need to deduct 1/24 from Now().

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
N

Norman Harker

Hi Himu!

Thanks for thanks. I hope the formula was of use.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Top