9

I am trying to display times in Google Sheets without an AM/PM indicator, but with 12-hour time (not 24-hour "military-style" time). I do not need the AM/PM indicator as it is redundant in my situation.

I'm currently using a custom date and time format:

[Hour (1)]:[Minute (01)][AM/PM (p)]

...which displays 2:15 PM as "2:15p".

If I change this to:

[Hour (1)]:[Minute (01)]

...it displays 2:15 PM as "14:15". However, I do not want 24-hour time.

Is there any way to leave off the AM/PM indicator while still displaying the hours in 12-hour time format?

I'd rather not do the formatting in a formula (converting the values to text) as my times are consumed by formulas in other cells.

2
  • Do you need time of the day o number of hours (duration)? – Rubén Jan 18 '16 at 23:27
  • @Rubén I need time of day. – Laurence Gonsalves Jan 19 '16 at 4:45
5

It's not possible without a formula. Separate time storing from time displaying by using one cell to store the time and another to display it in the desired format by using a formula like the following

=HOUR(A1)&":"&TEXT(MINUTE(A1),"00")
3

Good question: I just looked expecting to find an option under Format > Number > More Formats > Custom Number Format but couldn't see anything that would work.

If you do end up doing it with a formula, then the easiest approach may be to use helper columns, and something like =if(hour(E6) > 12, hour(E6)-12, hour(E6))

(Not the answer you want, I know, but I cannot see another, and couldn't find one by experimenting either.)

1

This is a total kludge, but if you want to do it without an extra column in your table, you can edit the format (Format > Number > More Formats > More Time and Date Formats) and then just insert a ton of spaces after the minutes and use the column size to hide the AM/PM part.

E.g., (Hour 1)(Minutes 01)(AM/PM a) -----> (Hour 1)(Minutes 01) (AM/PM a)

Then, just hide the am/pm text with column size.

There's a ton of reasons why this will eventually cause greater irritation than it is worth, but for something quick and dirty, I have found it to be convenient.

0

You don't need a formula; it's actually quite simple. If you input time is like this:
2:30 PM (with a space in the middle),
it will automatically switch your input to the 24hr equivalent. But if you input the time as 2:30pm (without a space in the middle), and THEN hit enter, it will automatically correct your input to 2:30 PM, as opposed to the military time format.

0

I hope this helps although I am not knowledgeable yet about this:

=(IF(HOUR(TEXT(NOW(),"hh:mm"))<12,"",HOUR(NOW())-12))&":"&TEXT(MINUTE(TEXT(NOW(),"hh:mm")),"00")

I am open for feedbacks although I am late in answering this.

0

I hope this helps although I am not knowledgeable yet about this:

=(IF(HOUR(TEXT(NOW(),"hh:mm"))<12,"",HOUR(NOW())-12))&":"&TEXT(MINUTE(TEXT(NOW(),"hh:mm")),"00")&" "&UPPER(IF(HOUR(TEXT(NOW(),"hh:mm"))<12,"am","pm"))

I am open for feedbacks although I am late in answering this.

-1

This will show the current time like 10:00 am:

=HOUR(TEXT(NOW(),"hh:mm"))&":"&TEXT(MINUTE(TEXT(NOW(),"hh:mm")),"00")&(IF(HOUR(TEXT(NOW(),"hh:mm"))<12," am"," pm"))
-2

Slightly kludgy works for goodenuf solution I just need it done:

  • Leave formatting as 12 hour time format with annoying AM/PM afterwards i.e. 11:59 AM
  • Right justify the cells
  • Narrow the column so AM/PM is not visible

Wide times like 12:45 cut off the 5 but not enough to matter - still can read it Everything else displays perfectly

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.