time zones

shaker

Board Regular
Joined
Jul 19, 2002
Messages
88
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Hero-0952

Wizard Deluxe
Joined
Jun 11, 2002
Messages
348
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

Board Regular
Joined
Jul 19, 2002
Messages
88
thnx Viper,
already seen the pearson site, but as i don't know how to make it work
 

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
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
 

Forum statistics

Threads
1,144,741
Messages
5,726,017
Members
422,653
Latest member
mntsiki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top