Formating Time - Keeps on Adding Date to Value

[cccontour]

Using the Data Validation Function in Excel, I created a list for Time. Cell References A1:A5 and are set to the format of "h:mm AM/PM"

Cell Formula Value
A1 11:57 PM 11:57 PM
A2 =A1+1/1440 11:58 PM
A3 =A2+1/1440 11:59 PM
A4 =A3+1/1440 12:00 AM
A5 =A4+1/1440 12:01 AM

Problem Scope
Selecting from the drop down list Cells A1 to A3 is just fine. Value displays as "h:mm AM/PM". However, Cells A4 & A5 automatically formats to "DD/MM/YEAR h:mm AM/PM". This is causing my time calculations to go way off. Can I can force the Cells A4 & A5 to be "h:mm AM/PM"?

Thanks!!

 

[Richard Schollar]

Hi Cccontour

Do you actually have the cells formatted as "h:mm AM/PM" thru regular cell formatting, because I can't replicate what you are seeing (ie it stays h:mm AM/PM even after 12am)?

 

[Andrew Poulsom]

That's because you have gone into the next day. Your time calculations should be fine. If you don't add a day you may get a negative time when subtracting, and Excel won't like it.

 

[cccontour]

Thank you! I do however need to make it not increase by a day though. Hopefully I can figure this one out