I am trying to get results for 4:00pm to 12:30am and i am just getting ############### I know that means the calcluation are incorrect but my same formula is working perfectly for 7:00am to 3:30pm. I also want this to subtract 30 minutes for lunch for anything over 6 hours. Can you help me?

I am trying to get results for 4:00pm to 12:30am and i am just getting ###############This formula will get a negative result, cause Excel assumes both given times are at the same day.

message edited by paulsep

Excel can not handle Negative Time, so you need to add 1 day

to your calculation.With your data like:

A B 1) 4:00 PM 12:30 AMTry this formula:

=IF(A1>B1,B1+1-A1,B1-A1)

See here for more on Adding/Subtracting Time:http://www.cpearson.com/excel/datea...

MIKE

Ask Your Question

Weekly Poll