My office works alot with Excel 2007, and we are beginning to save more of our files on Sharepoint. We have set up a couple of document libraries in Sharepoint and have linked some fields in our workbooks (most of which are somewhat structured) so that we can organize and filter these workbooks based on key fields. This has been accomplished via the custom properties feature of Excel 2007.
We have run into a strange problem that occurs when someone in our New York City office (timezone GMT -5) opens a file that has been created by someone in our Zurich office (timezone GMT +1). When the NYC employee opens the file, many of the date fields get turned back a day. For example, if the Zurich employee entered a date of 1/1/2011, the NYC employee sees the date as 12/31/2010, one day before. I have done a test by adding a time element to the date, and it seems like Excel is recalculationg the date by subtracting 6 hrs - the time zone difference.
Is there any way we can format the date cell so that Excel does not recalculate the date and time based on the local timezone? When formatting as a date, you'll notice that Microsoft highlights some date formats with a star, some without a star. The formats without a star are not supposed to be "affected by operating system settings". However, even when I use these unstarred date formats, the dates get changed according to timezone. We want the dates to be completely staic - ie timezone insensitive.
This problems seems to go away completely if I remove the custom property. In other words, with the link removed between the cell and custom property, the cell formatted as an unstarred date does not change. Therefore, the problem seems to be with the custom property linking. However, we need to keep this custom property in order to keep the Sharepoint functionality.
Anyone have any ideas? Thanks in advance for any help.
- Matt
We have run into a strange problem that occurs when someone in our New York City office (timezone GMT -5) opens a file that has been created by someone in our Zurich office (timezone GMT +1). When the NYC employee opens the file, many of the date fields get turned back a day. For example, if the Zurich employee entered a date of 1/1/2011, the NYC employee sees the date as 12/31/2010, one day before. I have done a test by adding a time element to the date, and it seems like Excel is recalculationg the date by subtracting 6 hrs - the time zone difference.
Is there any way we can format the date cell so that Excel does not recalculate the date and time based on the local timezone? When formatting as a date, you'll notice that Microsoft highlights some date formats with a star, some without a star. The formats without a star are not supposed to be "affected by operating system settings". However, even when I use these unstarred date formats, the dates get changed according to timezone. We want the dates to be completely staic - ie timezone insensitive.
This problems seems to go away completely if I remove the custom property. In other words, with the link removed between the cell and custom property, the cell formatted as an unstarred date does not change. Therefore, the problem seems to be with the custom property linking. However, we need to keep this custom property in order to keep the Sharepoint functionality.
Anyone have any ideas? Thanks in advance for any help.
- Matt
