Excel XML - DateFormat UDF

Posted By : todd sharp Posted At : January 11, 2007 11:00 AM Posted In: Excel Hacks, ColdFusion

0

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>

6042 Views Send Print Comments (0)

Comments (0)

Subscribe to Comments Add Comment

  • Calendar

    << March 2010 >>
    Sun Mon Tue Wed Thu Fri Sat
     123456
    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.