time difference formula

**Lukael** · 05-20-2014, 11:40 AM

Hi,

this is my formula for calculating time differences :

Please Login or Register  to view this content.

formula works, but It acts very odd when calculating with midnight hours (00:00:00). I have Combobox above start/end cells, which inserts selected time interval in them, and If I select time interval with midnight, formula doesn't calculate.

But If I select midnight hour manually from drop-down lists of start/end cells, formula works.

Looks like some problem with time format. How can I fix this ?

Please take a look at the sample, allready selected midnight from combobox.

thanks for all help !

**BrownBoy** · 05-20-2014, 07:38 PM

I noticed you're using military time for the data list in the Format Control "Input Range". I simple switched 12:00-00:00 to 12:00-24:00. If you keep your list as is then your interval is referencing "0" which messes with your formula by making your "TRUE" condition execute giving you a 0 answer. When you manually do it you aren't referencing military time so Excel can see the difference and give you the proper interval.

Formula_time.xls

Regards

**Lukael** · 05-21-2014, 12:58 AM

Hi BrownBoy,

I actually tried that before posting this thread, but It didn't work.

BUT now I see where I was wrong: in FORMAT cells. Because of hh:mm my VBA code didn't insert 24:00 in cell, but 00:00. Now changing to [h]:mm:ss and 24:00 is entered & everything works

thanks for help !