At 15:09 01/08/2007 -0500, Mark Saner wrote:
I am having trouble understanding how date formatting works in Calc. I
have a column where the date is entered in a MDYY format and when I try
to change it to MM/DD/YYYY I get very odd results. Take the value 7707.
I would like that to become 07/07/2007 however the date results in
02/05/1921.
Usually I can at least figure out how it is interpreting the number but
I am totally lost with this one. Any insights into how it is
interpreted? and is there a way to get it interpreted the way I want it
to be?
As you would expect, dates are stored internally by Calc simply as
integers, counting the days from a reference date. (This zero date
is adjustable, but the default is 30 December 1899.) When you enter
7707, this is stored as the integer 7707, despite whatever date
format you may have set. This is because "7707" is simply not
formatted as a date, so it is not interpreted as such. If the cell
format is a date format, this will display the 7707th day after the
reference date, which is 5 February 1921.
Actually, there can be no general answer to your question, since MDYY
is not a date format capable of dealing with all days in the
year. What happens if the date is after the 9th of the month, or the
month is October or later? You cannot just let this format expand as
necessary, or what will 11107 represent - 11 January or 1 November?
The simplest way out of this is to do what is normally done: to enter
the dates *as dates*, e.g. as 7/7/07 or 7-7-07 or 7.7.07. (These all
work in my locale.) Then you can reformat them any way you like. If
you have a lot of data already entered which you need to process,
that's a different problem ...
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
