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?
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:
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?
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.