Forum Discussion
Data entry into date formatted cell not interpreting PM properly.
Hi,
It depends or on your OS default format for the time, or on which format you apply to the cells in Excel (see Create custom format in same article).
So, if you apply custom format to your cells like h:mm AM/PM and enter 2:20 pm - it'll be interpreted as time.
Thanks for the speedy and thoughtful reply, Sergei.
Everything you describe is consistent with my previous experience with Excel, but not my current one. I just tried this to test:
1) Start excel and create a new workbook.
2) Click on cell A1 and assign it the time format "01:30:55 PM" (Format > Cells > Number > Time > 01:30:55 PM)
3) Enter 2:20 PM into the cell.
4) Enter the following formula into cell B1: =A1+time(1,0,0)
A1 shows what I typed in exactly (as it did when I tried "2:20 P" and "2:20 A"), and B1 shows a #VALUE! error. (See screen shot.) "2:20" becomes "2:20 AM" as one would expect, but nothing with PM or AM in it is interpreted properly.
Do you have any additional suggestions?
In A1 you definitely have the text (value aligned to the left and with format you specified it shall be 02:20:00 PM if it is recognized as time).
Another story why. Could be "PM" not English characters, perhaps something else. Please try to type simply 2:20 without any letters. If works, when something is with text part. If doesn't work, when formatting.
Thanks Sergei,
2:20 (or any other similar time up to 24:00) gets interpreted correctly. But as soon as a letter is included the whole thing is interpreted as text. I have tried 2:20 AM, 2:20 P, 2:20 p, 2:20 pm. All of these are interpreted as text.
Could you please attach small file with couple of cells filled as above