Cannot calculate times with 12:00 AM

Super User Asked by Fixdmix on December 7, 2020

I have a spreadsheet the successfully adds two times together, but that has one problem. I am adding two times to get a resulting time elapsed as in this sheet:

Sample sheet

The formula I’m using (in column F) to calculate is:


(I then use =HOUR(F4)+(MINUTE(F4)/60) in column G to convert to a number I can use in other calculations)

The cell format I have been using for all three cells is category – Time: 6:00 PM

My problem… arises when, as in row 8, a value of 12 am is entered. In this case, the value in the formula bar will show 12:00:00 AM, but the cell will display as blank (I assume because zero values are set not to display) and column F will not calculate and will be blank also. Any other time but midnight works without a problem.

In order to remedy this, I have tried different number formats such as [h]:mm, but still nothing works.

Can anyone please tell me how I can enter a time of midnight / 12:00:00 AM and have it be calculated as 12, and not zero? Note: I would prefer to have times formatted as 12hr and not 24 hr.

3 Answers

Was having the same issue. Tried to change the time format to: Custom - [h]:mm and it will show you 24:00. You will be able to do calculations like that.

Answered by Aleja on December 7, 2020

Having the same problem. With a cell formatted for "1:37:30 PM" or just "1:37 PM" any entry of 12:00 am results in a blank cell displayed, even though 12:00:00 AM is shown in the formula bar.

My solution is to enter the 12:00 am as "24:00" and this successfully displays at 12:00 am under those formatting conditions, with 24:00:00 shown in the formula bar.

Answered by Doug on December 7, 2020

Have played around with this to try and emulate your problem, And I think I see the issue. Try this:

I have formatted the cells to be Time - using 1:30:55PM as the specific option (the last of 5 options for Time formatting) and this works every time.

For me, if I set the time to

  • 00:00
  • 0
  • 24
  • or even to 23:60 (I was trying to make it break :-) )

it displays as 12:00:00 AM.

Answered by Rory Alsop on December 7, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP