Hi Guys!
Playing around with Excel's Web Query, I've discovered that when you format
a cell as a date and save it as a web page, Excel encloses that cell in
table data tags and adds a number to the tag's attributes like so:
<!-- This is my Clock In field -->
<td class=xl34 x:num="37235.333333333336">12/10/01 8:00 AM</td>
<!-- This is my Clock Out field -->
<td class=xl34 x:num="37235.708333333336">12/10/01 5:00 PM</td>
I guess this number represents the date in Microsoft Excel date-time code.
If this is true then the integer part represents (on my wintel system) the
number of days after January 1, 1900 (plus one). And I guess 8 am is about a
third of a day, so the fractional part seems right, though incorrectly
rounded at 10E-12.
I also discovered, looking in the style sheet created by Excel, that if you
don't have the mso-number-format in your style correctly, Excel won't format
the date-time code correctly. So it's just a matter of:
<!-- This is my Clock In field -->
<TD
STYLE="mso-number-format: m\/d\/yyyy\\ h\:mm\\ AM\/PM"
X:NUM="#IncrementValue(DateDiff('d', CreateDate('1900', 1, 1),
MyHoursQuery.Time_In))#.#Round((((Hour(MyHoursQuery.Time_In)*60)+Minute(MyHo
ursQuery.Time_In))*1000000000000)/(24*60))#"
>#DateFormat(MyHoursQuery.Time_In, Application.DateMask)#
#TimeFormat(MyHoursQuery.Time_In, Application.TimeMask)#</TD>
<!-- This is my Clock Out field -->
<TD
STYLE="mso-number-format: m\/d\/yyyy\\ h\:mm\\ AM\/PM"
X:NUM="#IncrementValue(DateDiff('d', CreateDate('1900', 1, 1),
MyHoursQuery.Time_Out))#.#Round((((Hour(MyHoursQuery.Time_Out)*60)+Minute(My
HoursQuery.Time_Out))*1000000000000)/(24*60))#"
>#DateFormat(MyHoursQuery.Time_Out, Application.DateMask)#
#TimeFormat(MyHoursQuery.Time_Out, Application.TimeMask)#</TD>
Creating a cell in Cold Fusion that gives you the difference between these
two in fractional hours in Excel is a little tricky. If you try to reverse
engineer it from Excel, Excel will give you the absolute cell references.
This would be a pain to keep track of in Cold Fusion. Luckily, it's just a
matter of creating a formula that uses relative references. So it's just a
matter of:
<!-- This is my hours column -->
<TD
STYLE="mso-number-format: \##\\ ?\/4"
X:FMLA="=(INDIRECT(ADDRESS(ROW(), COLUMN() - 1)) - INDIRECT(ADDRESS(ROW(),
COLUMN() - 2))) * 24"
>#Variables.HoursColumn#</TD>
The only hang here is that when you want 1/2, Excel gives you 2/4 instead. I
suppose the fact that I didn't have to go through all the rigamorole to get
Variables.HoursColumn into fractional hours in Excel like I did in Cold
Fusion should be of some confort.
Anybody out there have any better/faster/cheaper ways of prepping an HTML
output to be pushed live into Excel?
Thanx
Dave
______________________________________________________________________
Why Share?
Dedicated Win 2000 Server · PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation · $99/Month · Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
