How to format times between 12:00 am to 12:59 am (Updated)

**smonzon** · 01-25-2013, 11:54 PM

The attached file is a sample of a timesheet I’m working on. If I enter military time in cell between E4 to N4 and from E9 to N9, it is automatically converted to hh:mm format. For instance 1300 becomes 1:00 PM.

However, when I enter a time between 12:00 AM to 12:59 AM in a cell, the value inside the cell changes from "hh:mm:ss AM" to "1/1/1900 hh:mm:ss AM".

For instance 2400 become "1/1/1900 12:00:00 AM" instead of 12:00:00AM inside the cell. Because of this change, I get a ########## error instead of the number of hours between two times as shown in cells G10 and M10.

Is there a way to get around this problem? Thanks in advance for any suggestions.

**protonLeah** · 01-26-2013, 01:48 AM

In military time, the day starts at 00:00 hrs not 12:00 AM. Notice that you have 12 AM after 11 AM! --> The hour after 11 AM is 12 PM or 12:00 military. Then you have 12 pm (noon) after 11 pm...

**FDibbins** · 01-26-2013, 01:59 AM

Keep in mind that excel treats time as fractions of 1 day...starting at 0:00 (midnight) and working through to 23:59 (1 minute before midnight). In other words 24 hrs (1 day) = 1, 12 hours = 0.5 or a day, 4 hours = .25 of a day etc etc.

to test this, enter any time into a cell - it should automatically be reflected/formatted as time. then format that cell as a number, you will see that the time is now a decimal value. Formatting just changes the way the contents of a cell looks, it does not change the actual contents - working with time is a little different in that, when formatted as time, it actually looks like the cell contents have changed, but if you format back to a number, the decimal is still there.

Hope that helped a bit?

**smonzon** · 01-26-2013, 04:08 AM

Thanks for the information protonLeah and FDibbins, but I've just realized that I attached the wrong file. I have uploaded the correct one this time. On the other hand, I was able to solve the problem by using the MOD function. For instance, typing =MOD(H9-G9,1) in cell G10 provides the correct value. You are correct FDibbins, working with time in Excel is a little more complex. Thanks again.