Excel XML - DateFormat UDF

I blogged a while back about using the XML Spreadsheet schema to create spreadsheets in ColdFusion. I believe Ben Nadel has posted a few posts about this recently too. A coworker was using this technique today and found an issue when trying to output dates. Here's the issue:

He was using the ss:Type of 'DateTime' and was noticing that Excel didn't like the following value:

I suspected that Excel was being picky about the format of the date and did not recognize mm/dd/yy as such. I confirmed by changing the Type to 'string' - but that didn't quite solve the problem to my liking. I decided to do a quick test by creating a blank spreadsheet, entering a date, applying a mask in Excel and saving as XML. Viewing the generated XML told me that excel was expecting the following format:

With that in mind I whipped up this super easy UDF to handle the excel date formatting:

Comments (0) | Print | Send | Code | del.icio.us | Linking Blogs | 2807 Views