Data entry into date formatted cell not interpreting PM properly.

[CaptainModerate]

I'm running XL version 16.20 under Office 365 for Mac (OSX 10.14.2), and have encountered a problem over the past few months which I don't believe was present before.

In a spreadsheet I have columns of cells with date formatting applied (h:mm).  According to previous experience, and this article -- https://support.office.com/en-us/article/format-numbers-as-dates-or-times-418bd3fe-0577-47c8-8caa-b4... -- I should be able to enter a time with AM or PM and expect Excel to interpret the time appropriately.  Instead, if I enter "2:20 PM", it interprets this as text.  Calculations based on the contents of the cell therefore do not work.  The only way I have been successful is by entering "14:20" (which is what I ultimately want displayed).

Is there some way to get Excel to once again interpret "PM" as part of a time entry?

[#2]

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.

[#3]

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?

[#4]

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.

[#5]

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.

[#6]

Could you please attach small file with couple of cells filled as above