Conditional time format

[jkatj]

Hello,

I'm trying to find a way to change the time format applied to a time depending on whether the time is straight up X o'clock or not. So if the time is exactly 4:00 PM, I want the time to show as 4 p.m. (no colon, no zeros). If the time is 4:15 PM, I want it to show as 4:15 p.m. I've found a way to switch from the default PM to my company's preferred format of p.m., but I can't figure how to get the minutes to disappear only when it's exactly :00.

Does anyone have any suggestions on how to accomplish this?

Thanks much,

jkatj

[#2]

You may apply conditional formatting rule with formula like

=INT(A1*24)=A1*24

to your range and apply custom time format (which returns as 4 p.m.) to the rule

[#3]

Thank you, Sergei. That is beautiful. Unfortunately, given how I've had to manipulate my data to get the time to show "a.m." rather than "AM," I can't get it to work.

The initial problem is that I haven't found a way to get an actual custom time format to do "h:mm a.m./p.m." No matter what I've tried, I can't get it to put the periods in. So to accomplish that, what I've got is a hidden column (I) that calculates AM/PM and displays that as "a.m." or "p.m."
=IF(MOD(E3,1)>=0.5,"p.m.","a.m.")

And I've got another hidden column (F) that subtracts 12 from afternoon times so I end up with h:mm but not as 24-hour time:
=IF(MOD(E3,1)>=0.5,TEXT(E3-12/24, "h:mm"),TEXT(E3, "h:mm"))

Finally, I have a column that concatenates those together along with the date in the format I need:

=CONCATENATE(TEXT(D3,"mm/dd/yy"), " ", (TEXT(F3,"h:mm")), " ", I3)

There is probably a much more elegant solution, but that's the best I was able to come up with using my limited set of skills. I can't seem to find a way, either by conditional formatting or adding another link in my chain of calculations that uses the formula you came up with to get me where I'm going. I can get it to return 4PM as 16 or 4:15PM and 16:15, but I can't get it to show as 4 p.m. and 4:15 p.m.

Do you have any suggestions to get me to my desired and result?

Thanks so much!

[#4 · Accepted solution]

Hi,

You may apply default custom format to all your times as

h:mm a/p".m"

and on the top for the conditional formatting rule with formula as in previous post to apply similar format but without minutes

Please check attached file

[#5]

Sergei, that is perfect! I'd looked all over for something like that, but all I found was someone claiming that h:mm "a.m./p.m." would work. As soon as I saw yours, I was like, "Of course!" I really appreciate your help. I've learned so much. Thank you thank you thank you.