Show AM PM Not Military Time

[steve the excel guy]

Hi all,
I melted my eyes and brain reading through pages in this forum referring to TIME.

In short, I don't want to type Military Time. I tried to create a formula to show PM if the time is greater than 12:00 or AM if it is less as follows:

=IF(K4>"12:00"," PM"," AM")

This returns "AM" no matter what time is placed in K4... even if Military time were entered. So I changed it to a "<" sign. Now, it returns a "PM" no matter what.

I even converted it to:

IF(TEXT(K4>"12:00","hh:mm")," PM"," AM")

Same output.

 

[iggydarsa]

did you try formatting the cell?

ctrl +1 > Time > 1:30PM

 

[xenou]

If your cell values are true times, this is a matter of formatting.

Take a cell, hit Control + ~, and let me know what is actually in the cell...(this puts it into General formatting, or basically, no formatting).

Regards.

 

[steve the excel guy]

I'm not sure what you mean, if you mean a custom format using what you typed, it won't allow me to enter that.

I should also mention that the formula I showed below is within a concatenate formula so it will show something like "I called John at 11:30 AM".

=concatenate("I called John at "&IF(K4>"12:00"," PM"," AM"))

 

[steve the excel guy]

If your cell values are true times, this is a matter of formatting.

Take a cell, hit Control + ~, and let me know what is actually in the cell...(this puts it into General formatting, or basically, no formatting).

Regards.

lol... after I made the last response, I saw your statement. I tried the Control ~ anyway and it put the entire page into general formatting and UNDO won't fix it. DANG IT!
UPDATE: Hitting Control ~ again fixes it.

But to answer your question. K4 shows 0.45833333333 but if you click in it, it shows 11:00:00 AM

 

[iggydarsa]

give this a try

=CONCATENATE("I called John at "&IF(HOUR(K4)>=12,IF(HOUR(K4)=12,TEXT(K4,"hh:mm")&" PM",TEXT(K4-TIME(12,0,0),"hh:mm")&" PM"),IF(HOUR(K4)=0,TEXT(K4+TIME(12,0,0),"hh:mm")&" AM",TEXT(K4,"hh:mm")&" AM")))

 

[steve the excel guy]

Wow! That's a mouthful (so to speak). It works. I didn't imagine it would be such a long code for such a simple adjustment, lol.

Thank you very much!

 

[xenou]

I was thinking if you have real times (appears you do), it is actually easier - just use a time format, i.e,:

In Cell A1: .458333
In Cell B1: ="Called John at "&TEXT(A1,"h:mm AM/PM")

Note:
See Excel help under Guidelines for Custom Number Formats for more detail on time formats - with the TEXT() formula we just put the format in quotes as the second argument.

 

[steve the excel guy]

Sure, if you want to do it the easy way! Hahaha... Ok... I guess all I need to know was to add the AM/PM part in it. And I guess I didn't need the "concatenate" either.