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