Cannot recognize dates

[dasadler]

The attached worksheet has a date in col A. In col O the formula evaluates the date and if 12/31, returns a value of 3, if a Fri or Sat in Dec, returns a value of 2, if neither is true, returns a 1.

For some reason, the date in A3 is recognized but the date in A4 is not. I cannot understand why. Any ideas?

[Leif]

It may have something to do with localisation, but when I view the dates, A3 is in US format and A4 is in UK/European format.

x.jpg

If I enter 31-12 in A4, it displays as '31/12/2010' and a '3' is shown in O4.

Does that give you any clues?

[dasadler]

I did make a change in localization earlier today to make my computer display time in a 24 hour format. I wonder what else I changed???? Better go look.

I was unable to duplicate what you did. They both appear in US format to me... MM/DD/YYYY

[HansV]

If you undo the horizontal centering of column A, you'll see that A3 is right-aligned (correct) but that A4 is left-aligned, meaning that although it looks like a date and is formatted as a date, Excel sees it as text.
To correct this:
- Select an empty cell.
- Copy it (Ctrl+C)
- Select the cells with dates.
- Right-click them and select Paste Special... from the popup menu.
- Click Add, then OK.

[dasadler]

Thanks Hans,
however copying an empty cell and paste special add doesn't seem to make a difference.

[dasadler]

BTW, you're correct that Excel must see it as text because it does left align yet it is cell formatted as date. How does that happen?

[HansV]

Paste Special with the Add option does work when I try it in your sample workbook...

Are you sure your system date format is still m/d/yyyy?

Numbers and dates are sometimes seen as text if they are imported/copied from another application.

[dasadler]

yes, system date is M/d/yyyy. I notice that I am unable to enter a valid date in any of the subsequent rows in col A despite that I preformatted as date.

[dasadler]

I can copy A3 into A4 and it works correctly. However, if I edit the date in A4 that I just copied there... change the day from 18 to 31, it converts to text.

[HansV]

Everything you describe points to a European system date setting dd/mm/yyyy or similar. If I set my system to dd/mm/yyyy, I see the same behavior as you; if I set it to mm/dd/yyyy, the copy/paste special method works correctly. So I'd double check the system setting.

[dasadler]

Okay, it works now. The system date was US however, I had the time set to 24 hours (HHmm) and that made the difference. That sounds like a bug to me as the date and time should be independent, no?

[HansV]

Yes, they should be independent of each other. Weird...

(I have my work PC set to US date format mm/dd/yyyy but with 24-hour time format hh:mm, and the "text" dates were converted to "real" dates without problem)

[dasadler]

It seems that this only happens when the long time format is set such that there are no separators between hours, minutes, and seconds. The short time format seems to have no effect.

Can anyone else duplicate this issue by changing the long time format?

[HansV]

Yes, when I remove the time separator, Excel doesn't recognize date values stored as text anymore.

In general, it is not a good idea to remove either the date separator or time separator.