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.

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


"kojak" wrote in message
...
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.

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


"kojak" wrote in message
...
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.

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)))

"kojak" wrote:

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.

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.

"Eric" wrote:

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)))

"kojak" wrote:

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.

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.

"Bernie Deitrick" wrote:

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


"kojak" wrote in message
...
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.

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

"kojak" wrote:

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.

"Eric" wrote:

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)))

"kojak" wrote:

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.

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

"Eric" wrote:

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

"kojak" wrote:

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.

"Eric" wrote:

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)))

"kojak" wrote:

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.

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")



kojak wrote:

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.

--

Dave Peterson

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

"Dave Peterson" wrote:

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")



kojak wrote:

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.

--

Dave Peterson