Solved Calculating time in excel

February 20, 2014 at 15:23:30
Specs: Windows 7
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?


See More: Calculating time in excel

Report •


#1
February 20, 2014 at 15:37:53
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


Report •

#2
February 20, 2014 at 18:39:56
✔ Best Answer
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 AM

Try 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

http://www.skeptic.com/


Report •
Related Solutions


Ask Question