Date format problem

  • Thread starter Thread starter Brian Tozer
  • Start date Start date
B

Brian Tozer

I have a column of dates where one entry behaves as though it is formatted
differently than the others.
I have tried everything I can think of playing with the cell formatting of
that cell and the others in the column, clearing the cell formatting and
data and re-entering etc to no avail.
The format is 'date 3/14/01' or 'custom mm/dd/yy' either behave the same.
The faulty cell will display normally, the same as the others, but if I
display the page in formula mode, the good ones display as a numeral,
whereas the faulty one still displays as a date.
I have another column which uses a formula to display the month in text
derived from the date column.
This works correctly for all except the faulty entry, which displays as a
date instead of the month in text.
I have run out of ideas on how to solve this problem.
Any ideas?

Thanks
Brian Tozer
 
Brian

Excel is not seeing this as a true date for some reason. A date WILL show
as a number. What exactly is in this cell; it is the data, not the
formatting. How does it compare to the other entries ?

Regards

Trevor
 
Trevor said:
Brian

Excel is not seeing this as a true date for some reason. A date WILL
show as a number. What exactly is in this cell; it is the data, not
the formatting. How does it compare to the other entries ?
Click to expand...

Thanks Trevor. Your reply showed me what I needed to concentrate on and the
problem is now solved.
The confusion was exacerbated by Excel displaying the data very differently
than how it was interpreting it.
For example I finally needed to enter in NZ format and with 4 characters for
the year, although it displayed it in USA format and with 2 characters for
the year.
Even though the format was mm/dd/yy.
Thanks again.

Brian Tozer
 
Brian

glad I could help, if only by pointing you in the right direction. Maybe
you need to check your Regional Settings, etc. And maybe the default
settings for dates and times?

Regards ... and best wishes for the New Year

Trevor
 
Back
Top