Solved

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

Posted on 2014-11-23
6
1,066 Views
Last Modified: 2014-12-04
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
0
Comment
Question by:WeThotUWasAToad
6 Comments
 
LVL 12

Expert Comment

by:Alan3285
ID: 40461468
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
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40461493
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
 
LVL 32

Accepted Solution

by:
Rob Henson earned 333 total points
ID: 40461637
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 49

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 167 total points
ID: 40461769
@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
 
LVL 32

Assisted Solution

by:Rob Henson
Rob Henson earned 333 total points
ID: 40461808
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
 

Author Closing Comment

by:WeThotUWasAToad
ID: 40482237
Thanks.
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Alphabetical Order for Letters 2 21
Excel VBA 4 27
Select/Copy row and pasting it lower in sheet 7 20
Excel error  #DIV/0! 7 18
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question