The Excel properties predicament – A resolution..

Well thanks to a fellow SUGUK member, I found the solution to passing Date values from Excel into a SharePoint document library.

‘The devil is in the detail’ as the saying goes and it’s no truer in this instance.. The trick is making sure that the date format of the text you’re passing out of Esxcel matches what SharePoint expects to see in a date format library column. Basically it must meet a particular format of ISO8601, which basically equates to yyyy-mm-ddThh:mm:ssZ

ISO8601 specifies that dates should be formatted year, month then day, and times should be formatted hours, minutes then seconds. If they are stored in a single string, then they should be seperated by a Capital T. If a timezone is not specified, then the ISO8601 assumes local time. In order to specify GMT, we add Z for ‘Zulu time’ or ‘Zero Meridien’.

So, how do we access this type of date? Well within VB or C# code, we can make a call to the Mirosoft.SharePoint.Utilities namespace and use the SPUtility.CreateISO8601DateTimeFromSystemDateTime method. Unfortunately, this isn’t as easy to achieve from VBA code within Excell 2003. But, we don’t have to..

All you need is a simple text format command, SharePoint will happily accept a properly formatted string and then treat it as a date..

=TEXT(cell_reference,”yyyy-mm-ddThh:mm:ssZ”)

Where cell_reference is a named cell containing a standard date/time.

Now, create a named cell reference for the new cell, Head to the properties page for the spreadsheet and create a linked property that points to the new cell. Make sure you have a library column that matches the named property, then upload the spreadsheet to your document library et voila, your date will appear..

Leave a Reply

Your email address will not be published.

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.