PDA

View Full Version : Displayin Date/Time in "original" time zone



Rasm
04-15-2012, 07:24 AM
I have date/times that were recorded in another time zone - So I want to be able to read what time zone the data were recorded in - next I want to display the date/time in the "original" date/time or local time. In other words - the data was recorded in Germany and I open the sheet in New York - I want the date/time to be shown in German time and/or New York time - is there a property I can set to control how the date/time is displayed.

I want to do this via VBA code

Rick Rothstein
04-15-2012, 07:49 AM
While both New York and all of Germany observe Daylight Savings Time, the start and stop dates are different in each location... how did you want to handle that? Also, do you have a cell that tells which location the times are to be considered in? If so, what sheet/cell is that in? By the way, in case it will matter, what city or cities in Germany are you using?

Rasm
04-15-2012, 06:42 PM
Rick
I get data from all over - the records(rows) are date/time stamped from a process (production) - so when I report back my findings - I want to lock in the date/time stamp to what ever time zone the data was collected in. So regardless of what part of the world the sheet is viewed - the date/time stamp is locked and displayed as it was at the site where the data was originally collected. I hope this makes sense. Currently I add a ' in front of the date/time and then format it as a text field - But that way I have to use a format of YYYYMMDD hh:mm:ss - otherwise I cannot sort the date by time.
I hope this makes it clear what I am trying to do. So In a perfect worl I want the date/time to be displayed in using the time zone where it was collected - but formatted to the PC that is opening the sheet. I.e. the Europeans use military time and not AM/PM

Rick Rothstein
04-21-2012, 01:38 AM
Sorry about the delay getting back to you... I lost track of this thread. :feelingbeatup:

There is information about getting timezone information here...

Untitled Page (http://www.cpearson.com/excel/TimeZoneAndDaylightTime.aspx)

I am guessing you would want to put this in a cell next to the time when the time is entered? If so, then you would want to do that using event code.

Rasm
04-21-2012, 02:02 AM
Thanks Rick - yeah I was afraid I had to do a lot coding - but that is very good info - much appreciated