Format time cells in Excel to display AM/PM times but not the AM or PM

Hello,

In Excel (2013), is it possible to format a cell so that it displays AM/PM times but not the AM or PM?

For example, in both the Time & Custom sections of Cell Formatting, I found several different options for 24 hour (ie military time) formatting:

        11:30, 12:00, 12:30, 13:00, 13:30, 14:00

and several options for AM/PM formatting:

        11:30 AM, 12:00 PM, 12:30 PM, 1:00 PM, 1:30 PM, 2:00 PM

However, I'm trying to find a formatting setting which matches the AM/PM example but without the AM/PM:

        11:30, 12:00, 12:30, 1:00, 1:30, 2:00

In other words, I'd like to have all times >= 1:00 PM to display as 1:00, 1:30, …etc.

Thanks
WeThotUWasAToadAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
Assuming the time in F9, the following will convert to non AM/PM time. In fact it forces the time to time before noon.

=IF(F9>0.5,F9-0.5,F9)

Date and Time in Excel are represented as an integer for the date and the decimal part of the number for the time; 0.5 being midday. Therefore, if the time is after midday if you deduct 0.5 from it it will be the equivalent time in the morning which for display purposes will do what you need.

Then format the time with a standard hh:mm format.

Thanks
Rob H
0
 
AlanConsultantCommented:
Hi,

Maybe not directly, but you can do it like this (time is in A1):

=SUBSTITUTE(SUBSTITUTE(TEXT(A1,"h:mmAM/PM"),"a.m.",""),"p.m.","")

If your 'AM / PM' formats differ, then change the entries in the substitutions.  For example (without the periods between the am and pm):

=SUBSTITUTE(SUBSTITUTE(TEXT(A1,"h:mmAM/PM"),"am",""),"pm","")

Note that the result is a string, rather than a time value, but if you are doing this for display purposes, rather than for any further calculation, that might not be a problem for you.

HTH,

Alan.
0
 
Rgonzo1971Commented:
Hi,

You could customize you format in Format / Custom  

hh:mm[line feed] AM/PM

Your cell must have wrap cell to be able to show multiline cells therefore hide the second one

to enter the Line feed : Alt+0010 at the numeric Pad
see example

Regards
EE20141124.xlsx
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
Rgonzo1971Connect With a Mentor Commented:
@Rob

your formula does not take account that the midday should be seen as 12:30

instead use (if the time is a date as well)

=IF(ROUND(MOD(A1,1),9)>=ROUND(TIME(13,0,0),9),MOD(A1,1)-TIME(12,0,0),MOD(A1,1))

if not

=IF(ROUND(A1,9)>=ROUND(TIME(13,0,0),9),A1-TIME(12,0,0),A1)



Regards
0
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
To allow for 12:00 to 12:59 to show as 12 rather than 0, similar to Rgonzo:

=IF(F9>TIMEVALUE("12:59"),F9-0.5,F9)

As Rgonzo says, if cell also includes date then it will need stripping out using MOD as suggested.

Thanks
Rob
0
 
WeThotUWasAToadAuthor Commented:
Thanks.
0
All Courses

From novice to tech pro — start learning today.