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? |
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? |
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? . |
All times are GMT +1. The time now is 09:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com