Cannot calculate times with 12:00 AM

Question

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:

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

=IF(OR(D4=0,E4=0),0,IF((E4>D4),(E4-D4),((E4+24)-D4))

(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.

Aleja · Answer

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.

Doug · Answer

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.

Rory Alsop · Answer

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.

Cannot calculate times with 12:00 AM

3 Answers

Add your own answers!

Ask a Question