Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing time zones
Using a simple 'Range("XX") = Now' I can put todays date and time onto any
cell, but only in my time zone which is GMT. How do I change the time zone to, say EST which is 5 hours behind me? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing time zones
The time that Now inserts is not necessarily GMT - it is whatever time zone
you have setup in Regional Setting I have a subroutine with the line of code Range("D5") = Format(Now, "H:mm") that inserts the local time into D5 (I am on Atlantic time in Nova Scotia) If I replace that by two lines of code mytime = Now - (4 / 24) ' I like the parentheses because of the way VBA presents formula) Range("D5") = Format(mytime, "H:mm") Or the single statement Range("D5") = Format(Now - 4/24, "H:mm") it inserts the time in Vancouver (Pacific) which is 4 hours behind me And mytime = Now + (4 / 24) Range("D5") = Format(mytime, "H:mm") would insert the time in Exeter, Devon Be aware that when you go from UK to North America the dates for summer time differ a bit. So your code might need to add 5 most times and 4 or 6 when the two summer times are out of step. best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Darren" wrote in message ... Using a simple 'Range("XX") = Now' I can put todays date and time onto any cell, but only in my time zone which is GMT. How do I change the time zone to, say EST which is 5 hours behind me? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing time zones
Thankyou Bernard, that's just what I need.
"Bernard Liengme" wrote: The time that Now inserts is not necessarily GMT - it is whatever time zone you have setup in Regional Setting I have a subroutine with the line of code Range("D5") = Format(Now, "H:mm") that inserts the local time into D5 (I am on Atlantic time in Nova Scotia) If I replace that by two lines of code mytime = Now - (4 / 24) ' I like the parentheses because of the way VBA presents formula) Range("D5") = Format(mytime, "H:mm") Or the single statement Range("D5") = Format(Now - 4/24, "H:mm") it inserts the time in Vancouver (Pacific) which is 4 hours behind me And mytime = Now + (4 / 24) Range("D5") = Format(mytime, "H:mm") would insert the time in Exeter, Devon Be aware that when you go from UK to North America the dates for summer time differ a bit. So your code might need to add 5 most times and 4 or 6 when the two summer times are out of step. best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Darren" wrote in message ... Using a simple 'Range("XX") = Now' I can put todays date and time onto any cell, but only in my time zone which is GMT. How do I change the time zone to, say EST which is 5 hours behind me? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop down list & Time Zones. | Excel Discussion (Misc queries) | |||
Help with identifying time and time zones in a formula | Excel Worksheet Functions | |||
time zones | Excel Discussion (Misc queries) | |||
Does Excel support Time Zones? | Excel Worksheet Functions | |||
Time Zones | Excel Programming |