DATE FORMATS

[Lindsay Graham]

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. A
lot of people offered help, and I spent many hours applying the various
solutions offered, all to no avail.

The problem is that, whether in an existing file or a new file, dates
entered in the form d/m/yy (or just about any other date representation)
cannot be formatted as dates and therefore cannot be used in formulae. My
preferred format is the Custom d/m/yy_), but I've tried many other date
formats, and none of them work, whether the format is applied before or
after the data are entered.

I was using Vista Home Premium with Excel 2000. I have a home network, and
the other 2 computers have Excel 2000 running under Windows XP. There is no
problem with date formats on either of those computers, INCLUDING when I
open Excel files on the Vista computer (ie, a file where the problem existed
on the Vista computer) using Excel 2000 on a Win XP computer.

I became convinced that there was some sort of conflict between Vista and
Excel 2000, so I upgraded to Excel 2003. But the problem still exists.
Don't laugh -- it is seriously bugging me, and I'm wasting a hell of a lot
of time trying to fix the problem.

Does anyone have any bright ideas? -- please!!!!!!!!!!!!!!!!!!!!!!

 

[Pete_UK]

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

 

[Kevin B]

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.

 

[Lindsay Graham]

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?

 

[Pete_UK]

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