SOLVED
Home

Conditional time format

%3CLINGO-SUB%20id%3D%22lingo-sub-356425%22%20slang%3D%22en-US%22%3EConditional%20time%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-356425%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20find%20a%20way%20to%20change%20the%20time%20format%20applied%20to%20a%20time%20depending%20on%20whether%20the%20time%20is%20straight%20up%26nbsp%3BX%20o'clock%20or%20not.%20So%20if%20the%20time%20is%20exactly%204%3A00%20PM%2C%20I%20want%20the%20time%20to%20show%20as%204%20p.m.%20(no%20colon%2C%20no%20zeros).%26nbsp%3BIf%20the%20time%20is%204%3A15%20PM%2C%20I%20want%20it%20to%20show%20as%204%3A15%20p.m.%20I've%20found%20a%20way%20to%20switch%20from%20the%20default%20PM%20to%20my%20company's%20preferred%20format%20of%20p.m.%2C%20but%26nbsp%3BI%20can't%20figure%20how%20to%20get%20the%20minutes%20to%20disappear%20only%20when%20it's%20exactly%20%3A00.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20have%20any%20suggestions%20on%20how%20to%20accomplish%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20much%2C%3C%2FP%3E%3CP%3Ejkatj%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-356425%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-357941%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20time%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-357941%22%20slang%3D%22en-US%22%3E%3CP%3ESergei%2C%20that%20is%20perfect!%20I'd%20looked%20all%20over%20for%20something%20like%20that%2C%20but%20all%20I%20found%20was%20someone%20claiming%20that%3CSTRONG%3E%20h%3Amm%20%22a.m.%2Fp.m.%22%3C%2FSTRONG%3E%20would%20work.%20As%20soon%20as%20I%20saw%20yours%2C%20I%20was%20like%2C%20%22Of%20course!%22%20I%20really%20appreciate%20your%20help.%20I've%20learned%20so%20much.%20Thank%20you%20thank%20you%20thank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-357235%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20time%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-357235%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20apply%20default%20custom%20format%20to%20all%20your%20times%20as%3C%2FP%3E%0A%3CPRE%3Eh%3Amm%20a%2Fp%22.m%22%3C%2FPRE%3E%0A%3CP%3Eand%20on%20the%20top%20for%20the%20conditional%20formatting%20rule%20with%20formula%20as%20in%20previous%20post%20to%20apply%20similar%20format%20but%20without%20minutes%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20516px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F84492iDC4847659F2DC5BB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EPlease%20check%20attached%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-356954%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20time%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-356954%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%2C%20Sergei.%20That%20is%20beautiful.%20Unfortunately%2C%20given%20how%20I've%20had%20to%20manipulate%20my%20data%20to%20get%20the%20time%20to%20show%20%22a.m.%22%20rather%20than%20%22AM%2C%22%20I%20can't%20get%20it%20to%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20initial%20problem%20is%20that%20I%20haven't%20found%20a%20way%20to%20get%20an%20actual%20custom%20time%20format%20to%20do%20%22h%3Amm%20a.m.%2Fp.m.%22%20No%20matter%20what%20I've%20tried%2C%20I%20can't%20get%20it%20to%20put%20the%20periods%20in.%20So%20to%20accomplish%20that%2C%20what%20I've%20got%20is%20a%20hidden%20column%20(I)%26nbsp%3Bthat%20calculates%20AM%2FPM%20and%20displays%20that%20as%20%22a.m.%22%20or%20%22p.m.%22%3CBR%20%2F%3E%3DIF(MOD(E3%2C1)%26gt%3B%3D0.5%2C%22p.m.%22%2C%22a.m.%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20I've%20got%20another%20hidden%20column%20(F)%26nbsp%3Bthat%20subtracts%2012%20from%20afternoon%20times%20so%20I%20end%20up%20with%20h%3Amm%20but%20not%20as%2024-hour%20time%3A%3CBR%20%2F%3E%3DIF(MOD(E3%2C1)%26gt%3B%3D0.5%2CTEXT(E3-12%2F24%2C%20%22h%3Amm%22)%2CTEXT(E3%2C%20%22h%3Amm%22))%3C%2FP%3E%3CP%3EFinally%2C%20I%20have%20a%20column%20that%20concatenates%20those%20together%20along%20with%20the%20date%20in%20the%20format%20I%20need%3A%3C%2FP%3E%3CP%3E%3DCONCATENATE(TEXT(D3%2C%22mm%2Fdd%2Fyy%22)%2C%20%22%20%22%2C%20(TEXT(F3%2C%22h%3Amm%22))%2C%20%22%20%22%2C%20I3)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20probably%20a%20much%20more%20elegant%20solution%2C%20but%20that's%20the%20best%20I%20was%20able%20to%20come%20up%20with%20using%20my%20limited%20set%20of%20skills.%20I%20can't%20seem%20to%20find%20a%20way%2C%20either%20by%20conditional%20formatting%20or%20adding%20another%20link%20in%20my%20chain%20of%20calculations%20that%20uses%20the%20formula%20you%20came%20up%20with%20to%20get%20me%20where%20I'm%20going.%20I%20can%20get%20it%20to%20return%204PM%20as%2016%20or%204%3A15PM%20and%2016%3A15%2C%20but%20I%20can't%20get%20it%20to%20show%20as%204%20p.m.%20and%204%3A15%20p.m.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20have%20any%20suggestions%20to%20get%20me%20to%20my%20desired%20and%20result%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20so%20much!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-356453%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20time%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-356453%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20may%20apply%20conditional%20formatting%20rule%20with%20formula%20like%3C%2FP%3E%0A%3CPRE%3E%3DINT(A1*24)%3DA1*24%3C%2FPRE%3E%0A%3CP%3Eto%20your%20range%20and%20apply%20custom%20time%20format%20(which%20returns%20as%204%20p.m.)%20to%20the%20rule%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
jkatj
New Contributor

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

4 Replies

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!

 

 

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

image.png

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies