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:

<Data ss:Type="DateTime"><cfoutput>#dateFormat(now(), "mm/dd/yy")#</cfoutput></Data>

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:

yyyy-mm-ddThh:mm:ss.l

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

<cffunction name="excelDateFormat" output="false" hint="i take a cf date and format it as excel friendly" access="public" returntype="string">
   <cfargument name="theDate" type="date" required="true" hint="the date to make excel friendly">
   <cfreturn dateFormat(arguments.theDate, "yyyy-mm-dd") & "T" & timeFormat(arguments.theDate, "hh:mm:ss.l") />
</cffunction>

Comments

Calendar

Sun Mon Tue Wed Thu Fri Sat
  12345
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31   

Subscribe

Enter your email address to subscribe to this blog.

Tags

actionscript ajax blogging cfsnippets coldfusion flash forms flex misc model-glue off topic personal project learn slidesix sql

Recent Comments

More CF+Java: Compiling Classes And Persisting Objects
Getburl said: I have been attempting to get Db4o working in my CF application and I have not succeeded. I would lo... [More]

Thoughts On Ajax Frameworks And ColdFusion/Adobe
Erast said: http://fanniecollins.10gb... emo http://gracetrevino.phree...... [More]

Extending Ext With Ext Extensions
Erast said: http://fanniecollins.10gb... emo http://gracetrevino.phree...... [More]

CF Needs An Open Source Contact List Importer
Kay Smoljak said: Heh, the fact that sites DO it doesn't mean they SHOULD. To us it's ok, but to a non-tech-savvy user... [More]

A Few Project Updates
Helena said: Now punctually what is the situation ? [More]

RSS


coldfusionbloggers

FullAsAGoog MXNA

Consumed By Feed-Squirrel.com