Feb 25 2019 11:04 AM - edited Feb 25 2019 11:05 AM
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
Feb 25 2019 11:59 AM
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
Feb 26 2019 07:06 AM
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!
Feb 26 2019 01:35 PM
SolutionHi,
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
Feb 27 2019 03:49 PM
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.
Feb 26 2019 01:35 PM
SolutionHi,
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