On Wed, Mar 11, 2015 at 7:45 PM, Vicki Wilson <VWilson@xxxxxxxxxxxxx> wrote:
I'm googling and finding lots of specific examples - particularly one's
that include a full date timestamp - but nothing with just the date.
And nothing that really explains the mapping.
I'm curious what you've found so far. I am very leery of working
directly in XML when dealing with Excel. For one thing, there are at
least two distinct XML-related Excel formats. The current one, which
is a zipped archive whose name ends in .xlsx, and at least one older
one. The names of these are so routinely mixed up and imprecise that
it really behooves you to say, literally ".xlsx" or "the XML format
which is NOT .xlsx" to be crystal clear.
Anyway, the picture is quite simple if you take the time to understand
a little bit about dates in Excel. And I can explain it right now.
Dates are just a number of days from an epoch. The time is determined
by the fractional part of this number.
There isn't really such thing in Excel as "just" a date. There is a
float interpreted as a timestamp, with the time ignored. (And most
cells meant to contain "dates only" will have whole-number values,
equivalent to having the time portion be midnight.)
So then what you need to know is the epoch. There are two
possibilities. The main one is tricky because of an ancient
bug-by-design (ostensibly for compatibility with Lotus 1-2-3) which
incorrectly considers 1900 a leap year. But it's supposed to be that
Jan 1, 1900 is Day 1. Jan 2, 1900 is Day 2, etc. So Feb 28, 1900 is
day 59, the invalid and nonexistent Feb 29, 1900 is Day 60, and Mar 1,
1900 is day 61. If you don't care about supporting dates before Mar 1,
1900, you can just pretend, for calculation purposes, that Dec 31,
1899 was Day 1.
The other possible epoch is Jan 1, 1904. This is the default for Excel
sheets that come from a Mac.
Now, it may be that what you found in your searches already have
turned up other ways of getting a date value into Excel. They might be
correct; I don't know without seeing them. The most reliable way I've
found is to just figure out the raw number that Excel wants, and throw
that in there, and set the formatting of the cell appropriately.
The expression would be %diff(%date(MyDate:*iso):d'1899-12-30':*d)
(assuming MyDate is an 8-digit YYYYMMDD numeric value; you can of
course skip %date if you've already got a genuine date).
John Y.
midrange.com
