time zones

[shaker]

Hi every one,
i created this workbook and i added a cell with local time being Sydney. I also created one for Ney York and London and Tokyo. When local time switches to daylight savings it adjusts the time automatically because it reads of the computers time i guess. how to i get the aother clocks to also adjust to the daylight savings automatically. I'm not big on VBA so a detailed explaination will be needed on how to do it. I got an idea if it might help, if i know the time and days the other countryies change to day light savings would that help? ie.New York DST is from 1st sunday in April to last Sunday in October and London DST is from Last Sunday in March to last Sunday in October.
Thnx in Advance
shaker

 

[Hero-0952]

Hi Shaker.

MrExcel has added to his Articles Feature to include articles from sites all over the web. There are several articles dealing with the issue of Time on this web at: http://www.mrexcel.com/articles.shtml#VBA
When you hit the page just scroll to "Time" in the VBA section!

This article in particular may be just what you need: http://www.cpearson.com/excel/timezone.htm

Regards,

ViperGTS
This message was edited by ViperGTS on 2002-10-24 00:14

 

[shaker]

thnx Viper,
already seen the pearson site, but as i don't know how to make it work

 

[IML]

In your NY sheet, if time was in C2, I think
=+C2+"5:00"-IF(AND(TODAY()>=DATE(YEAR(NOW()),3,31)-(WEEKDAY(DATE(YEAR(NOW()),3,31))-1)*(WEEKDAY(DATE(YEAR(NOW()),3,31))<>1),TODAY()<DATE(YEAR(NOW()),3,31)+8-WEEKDAY(DATE(YEAR(NOW()),3,31))),"1:00",0)
would get you London time and
=+C2+"14:00"-IF(OR(TODAY()<DATE(YEAR(NOW()),3,31)+8-WEEKDAY(DATE(YEAR(NOW()),3,31)),TODAY()>=DATE(YEAR(NOW()),10,31)-(WEEKDAY(DATE(YEAR(NOW()),10,31))-1)*(WEEKDAY(DATE(YEAR(NOW()),10,31))<>1)),"1:00",0)
would get you sydney time.

I'm not sure if I maybe adding an hour when I should be subtracting. Basically this decrease the spread btwn london and NY from 5 to four hours during the week that is different. I'm assuming sydney doesn't recongnize daylight savings time, so this decreases the spread from 14 to 13 hours before the spring change and after the fall change. If this works the way you want, formulas for the other spreadsheets could be developed. I think VBA would be easier though...

edited to disable HTML
This message was edited by IML on 2002-10-24 14:54