SharePoint Document Library Date Time Columns May Be Incorrectly Changed for Office 2007 Documents if Opened and Saved by Office XP/2003

  • Article

Symptoms

If you create a document library in Microsoft Office SharePoint Server (MOSS) with a custom column of a Date/Time data type, and you store Office 2007 (Open XML) documents in that library, if the files are opened, edited and then saved back to the SharePoint library using Office XP or Office 2003, the date/time value of the custom column property may mistakenly be changed to an incorrect time when the file is saved.  The problem is an incorrect conversion of the date time value from UTC to local time and back again, causing the time value of the property to be altered plus/minus some time increment equal to the time difference between your local time and the GMT time zone.  The problem pertains only to custom properties associated with a SharePoint document library, and only if the file is converted at runtime for Office XP/2003 (it does not occur if client is using Office 2007).

Cause

The problem is a confirmed bug in the Office 2007 Compatibility Pack converters for Word and Excel.  During the conversion process from Open XML format into the native formats used by Office XP/2003, a call is made to check if the file contains custom document properties associated with SharePoint lists or columns. If found, the properties are re-evaluated and thereby modified.  Due to a bug in this process, the properties are (re)saved in the native file in local file time instead of UTC.  When the native document is then opened, edited, and re-saved, Office XP/2003 believes the new time value is in UTC and thereby converts the local time back into Open XML format as if it was the UTC time.  When the file is saved back to SharePoint, the incorrect time is updated on the server and SharePoint displays the new (incorrect) time in the document library column. If the process is repeated, you may continue to gain/lose hours for that column.

Status

The problem is being investigated for a future fix in Office 2007 SP2 for the Compatibility Pack.

Steps to Reproduce

1.) On your MOSS server, log in with an account that has Designer or Full Permissions to the MOSS site.  Create a new document library. Name the library TestLib, and change the Document Template setting to Microsoft Office Word Document (this is the *.DOCX file type).

2.) Once TestLib is generated, view the (empty) library and choose Settings | Create Column.  Name the column TestTime, choose Date And Time as the column type, then choose Date & Time for the Date and Time Format type, and Today's Date for the default value. Press OK to create the custom column property.

3.) Go to a machine with Office 2007. In a web browser, navigate to TestLib on your MOSS site, then choose New | New Document.  This should prompt you if you would like to create a new file.  Choose Yes.

4.) After the file opens in Word 2007, modify the file is some way, then choose Save.  In the SaveAs dialog, you should see the TestLib location is already selected for you, so you just need to provide a file name and click Save.  The file should be saved.  Close the file.

5.) Switch back to the web browser and refresh your view.  You should see your file listed and the TestTime column with today's date and time.

6.) Go to a machine with Office 2003 and the Compatibility Pack installed.  In a web browser, navigate to TestLib on your MOSS site, then find the document you saved previously.  Click the link to open the file in Word 2003 (you can also choose the "Edit In Microsoft Word" dropdown option).

7.) The file will auto-convert and open in Word 2003.  Make any small change you like to the document and press Save. Close the file.

8.) Switch back to the web browser and refresh your view.  You will see the file was updated (as expected) and the TestTime column value has changed (assuming you are in a different time zone than GMT) which is not expected.  The time value was not modified by the user, so should have stayed the same. It did not.  The amount of time difference (+/- X hours) will depend on your local time zone.