Changing time zones

[Darren]

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?

 

[Bernard Liengme]

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

 

[Darren]

Thankyou Bernard, that's just what I need.

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



.