asked on
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
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
Microsoft Excel
Last Comment
WeThotUWasAToad
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
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks.
Maybe not directly, but you can do it like this (time is in A1):
=SUBSTITUTE(SUBSTITUTE(TEX
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(TEX
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.