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?
Cannot recognize dates
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Cannot recognize dates
You do not have the required permissions to view the files attached to this post.
Don
-
- Administrator
- Posts: 7218
- Joined: 15 Jan 2010, 22:52
- Location: Middle of England
Re: Cannot recognize dates
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.
Does that give you any clues?
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?
You do not have the required permissions to view the files attached to this post.
Leif
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: Cannot recognize dates
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
I was unable to duplicate what you did. They both appear in US format to me... MM/DD/YYYY
Don
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cannot recognize dates
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.
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.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: Cannot recognize dates
Thanks Hans,
however copying an empty cell and paste special add doesn't seem to make a difference.
however copying an empty cell and paste special add doesn't seem to make a difference.
Don
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: Cannot recognize dates
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?
Don
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cannot recognize dates
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.
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.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: Cannot recognize dates
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.
Don
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: Cannot recognize dates
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.
Don
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cannot recognize dates
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.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: Cannot recognize dates
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?
Don
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cannot recognize dates
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)
(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)
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 889
- Joined: 25 Jan 2010, 16:26
- Location: Garden Grove, CA 92844 USA
Re: Cannot recognize dates
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?
Can anyone else duplicate this issue by changing the long time format?
Don
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cannot recognize dates
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.
In general, it is not a good idea to remove either the date separator or time separator.
Best wishes,
Hans
Hans