Omiting AM/PM

[Guest]

How can I get time to display without the AM or PM after it, using 12 hour
day? I am wanting something like 2:30 pm to display as just 2:30 and not as
14:30.
Thanks in advance.

 

[Bernie Deitrick]

Kojak,

You would need to use a helper column of formulas to subtract 0.5 from times that are after Noon.

HTH,
Bernie
MS Excel MVP

 

[Peo Sjoblom]

You can use text entries instead, of course then you can't make any
calculations
If you precede the entry with an apostrophe or preformat as text you can use
whatever you want. But you can't format a number as time without letting
Excel know what time of day it is. One hour in Excel is the number 1/24,
that means that 02:30 is 2.5*(1/24) whereas 02:30 PM is 14.5*(1/24) so the
values are not the same thus cannot be displayed the same


Regards,

Peo Sjoblom

 

[Guest]

I couldn't find a formatting solution. In the event nobody else can and you
don't mind displaying a dummy column referencing your real data, this will
show your data the way you want it when you format as 24-hour time.

Assuming your real data is in cell A1:

=IF(A1-INT(A1)>13/24,A1-12/24,IF(A1-INT(A1)<1/24,A1+12/24,IF(A1-INT(A1)=13/24,1/24,A1)))

 

[Guest]

I appreciate everyones help.
Eric I tried your formula, modifiying A1 to P5, but the answer that I got
for 4:30 pm was 0.1875. What I am wanting is for it to display 4:30 without
the pm.

 

[Guest]

The only challenge is that times (either original or derived after
subtracting 0.5) from 12:00-12:59 a.m. display looking like "00:59" in
24-hour format. Hence all the extra workarounds in my formula.

 

[Guest]

Format as 24-hour time. (Shows as "13:30" under the Format Cells-Time
selection)

 

[Guest]

Thanks Eric. That did the trick. That is one that I should have caught
myself. Again thanks for your help.

 

[Dave Peterson]

If you're willing to use that helper cell:

=LEFT(TEXT(A1,"hh:mm AM/PM"),5)
or
=TEXT(MOD(A1,0.5),"h:mm")

 

[Guest]

Thanks Dave, both of those do exactly what I was looking for.