Forum Discussion
Solved
Conditional time format
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
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
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
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!
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
