Does anyone know how Excel deals with time zones for dates when the workbook moves from a computer in one time zone to another in a different time zone? I'm looking for some input and advice about whether my analysis of the problem that I'm experiencing is correct, and for a way of handling the problem.
I live in New Zealand (GMT+12 at the moment) and I wrote a workbook that contains a column that's formatted as "ddd, mmmm dd, yyyy". I used US month-day-year order because the user of this workbook is based in the US.
I set up some initial data in the workbook, including the date column. These are dates of events so are specific and known. Once I was happy with the data and the VBA code, I shipped it to the US. Although the date column look correct there (so I'm told) when the code on the web site reads the sheet in the workbook, the dates sometimes come out specifying the date one day after the event.
What I think is happening is that when I enter a date here in New Zealand Excel works out the number of days since 01-Jan-1900 and stores that as an integer. In the column it looks correct once Excel has applied the formatting. When I ship the workbook to New Jersey, Excel now interprets that day count as an offset from 01-Jan-1900 but in Eastern Time. Given the 16/17/18 hours time difference (depending on the time of the year) between NZ and New Jersey, it means that for most of the day NJ is one day behind NZ. The result is that when NJ and NZ are in the same calendar day when I insert the data Excel in NJ reports the correct date; when NZ is a day ahead when I insert the data, Excel in NJ reports the date as being one after the true event date.
The problem seem to me to be that Excel does not store the time zone in the raw date format, or it doesn't take the time zone into account when working out the offset from 01-Jan-1900. If it always worked relative to 00:00 01-Jan-1900 GMT then the dates would be transferable across time zones. That would make life very difficult for date handling because we would all have to have time zone aware code and formulae for handling dates.
To compound this problem, the people in New Jersey who are using this workbook will be entering new events with dates and those are going to be based off Eastern Time.
I'm wondering if a solution to this problem is to have a VBA routine run once and once only when the workbook is first opened in New Jersey, that runs through the date column and adjusts them all for Eastern Time and saving that back into the cell. All new and edited dates thereafter should be based off local NJ time. If they send the workbook back to me for updates to the VBA I have a routine that does the reverse, converting from NJ base to NZ base. I then arm the run-once routine and send it back to them. Anybody have any thoughts on whether that scheme would work? Or, better still, is there an easier way of handling this problem? A slightly more sophisticated approach might be to detect the local time zone and make the adjustment if the time zone has changed since the workbook was last saved.
I am thinking that someone, somewhere must have run into this problem given the ubiquity of Excel. I'm using Excel 2007 and the New Jersey people are using Excel 365.
Thanks in advance for your thoughts.
Lummo
I live in New Zealand (GMT+12 at the moment) and I wrote a workbook that contains a column that's formatted as "ddd, mmmm dd, yyyy". I used US month-day-year order because the user of this workbook is based in the US.
I set up some initial data in the workbook, including the date column. These are dates of events so are specific and known. Once I was happy with the data and the VBA code, I shipped it to the US. Although the date column look correct there (so I'm told) when the code on the web site reads the sheet in the workbook, the dates sometimes come out specifying the date one day after the event.
What I think is happening is that when I enter a date here in New Zealand Excel works out the number of days since 01-Jan-1900 and stores that as an integer. In the column it looks correct once Excel has applied the formatting. When I ship the workbook to New Jersey, Excel now interprets that day count as an offset from 01-Jan-1900 but in Eastern Time. Given the 16/17/18 hours time difference (depending on the time of the year) between NZ and New Jersey, it means that for most of the day NJ is one day behind NZ. The result is that when NJ and NZ are in the same calendar day when I insert the data Excel in NJ reports the correct date; when NZ is a day ahead when I insert the data, Excel in NJ reports the date as being one after the true event date.
The problem seem to me to be that Excel does not store the time zone in the raw date format, or it doesn't take the time zone into account when working out the offset from 01-Jan-1900. If it always worked relative to 00:00 01-Jan-1900 GMT then the dates would be transferable across time zones. That would make life very difficult for date handling because we would all have to have time zone aware code and formulae for handling dates.
To compound this problem, the people in New Jersey who are using this workbook will be entering new events with dates and those are going to be based off Eastern Time.
I'm wondering if a solution to this problem is to have a VBA routine run once and once only when the workbook is first opened in New Jersey, that runs through the date column and adjusts them all for Eastern Time and saving that back into the cell. All new and edited dates thereafter should be based off local NJ time. If they send the workbook back to me for updates to the VBA I have a routine that does the reverse, converting from NJ base to NZ base. I then arm the run-once routine and send it back to them. Anybody have any thoughts on whether that scheme would work? Or, better still, is there an easier way of handling this problem? A slightly more sophisticated approach might be to detect the local time zone and make the adjustment if the time zone has changed since the workbook was last saved.
I am thinking that someone, somewhere must have run into this problem given the ubiquity of Excel. I'm using Excel 2007 and the New Jersey people are using Excel 365.
Thanks in advance for your thoughts.
Lummo
