Lindsay,

I'm about to go to bed now, but I'll ponder on what could be giving
you an 83-day discrepancy (plus 2/3 of a day, or 16 hours).

Pete

On Aug 28, 2:07*am, "Lindsay Graham" wrote:
Thank you, Pete and Kevin. *We now seem to be making some progress, although
sometimes in a wrong direction and not yet far enough.

Pete, Regional Settings had been checked several times, and they are OK,
with short date format set to d/M/yy and long date as, eg, 'Thursday, 28
August 2008'.

I then entered dates in a new spreadsheet as DATE(yyyy,m,d) as you
suggested, formatted them as d/m/yy_) and, hallelujah, it worked! *And the
resultant values were accepted in formulae. *But the big question is -- how
do I avoid that extra step and why is formatting not working as it should?

I then tried Kevin's suggestion, and got some weird results. *Using the same
spreadsheet, '1 January 2008' was displayed in that form, and DATEVALUE()
returned 39531, not 39448. *I then formatted the cell as d/m/yy and d/m/yy_)
and both formats were accepted and the cell values were accepted as dates in
formulae (hallelujah again). *But the date shown then was 24/3/08, and
consistent results were shown for different dates, ie, the displayed date
was always 83 days ahead of the date originally input.

However, I then tried Kevin's suggestion in a new spreadsheet, and '1
January 2008' was displayed as 39531.67 -- but how can a serial date have a
decimal component? *DATEVALUE() applied to that cell resulted in #VALUE!
Other long dates input gave the same result, ie, apparently 83 days ahead
and always with .67 added on. *If I pre-formatted a group of cells (eg, as
d/m/yy_) or dd/mm/yyyy) and typed '1 January 2008' it was displayed as
'24/3/08 ' or '24/03/2008' respectively and those cell values were accepted
as dates in formulae. *But, DATEVALUE() returned #VALUE!.

Any ideas on why such strange results, and how to fix it all?

--
Lindsay Graham
Canberra, Australia
---------------------------------------------
Please reply only to the list/newsgroup so that all may benefit.

"Kevin B" wrote in message

...



Have you attempted a conversion using the DATEVALUE function, which will
verify whether or not your dates are text values.

In a blank worksheet type the following date value in cell A1:

January 1, 2008

In cell B1 enter the following formula:

=DATEVALUE(A1)

The value of 39448 us returned.

You could use this formula to convert your date column to a date serial
number and then replace your original date column using copy/paste
special/values to copy/paste the DATEVALUE column.
--
Kevin Backmann

"Pete_UK" wrote:

Have you been into Control Panel and checked your Regional Settings?
This is available from the Windows Start menu, and Excel picks up some
of its settings from there.

Another approach is to enter a date (e.g. 27/8/2008) as
=DATE(2008,8,27), and then you should be able to format that cell as
you wish. A bit long-winded, but at least the value will be taken as a
date.

Hope this helps.

Pete

On Aug 27, 1:05 pm, "Lindsay Graham" wrote:
Back in June, I asked (under the heading "CELL FORMATTING WILL NOT
WOK") how
to resolve a problem I was having applying date formats in Excel 2000.