MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Subtracting Time


Posted by Paula on January 04, 2002 8:48 AM

I want to subtract 2 times and end up with total hours. For example: 9:00 AM subtract 3:30 PM = 6 1/2 hours. Thanks.


Posted by Aladin Akyurek on January 04, 2002 8:50 AM

In C1 enter: =B1+(B1 < A1)-A1

Custom format C1 as [h]:mm

Aladin

Posted by Paula on January 04, 2002 9:31 AM

Thanks Aladin, that definitely helps me. I would like to take it one step further. I have added a column that represents an amount of time taken for lunch. When I subtract 1 hour from C1 (in your example) it gives me number -.7291666. Is there a custom format to show that # as 5.5 hours?
I appreciate all your help.

Posted by Aladin Akyurek on January 04, 2002 9:42 AM

Paula --

Use either directly in C1

=(B1+(B1 < A1)-A1)-"1:00"+0

where C1 is still custom formatted as [h]:mm

or

in D1 enter: =C1-"1:00"+0

and custom format D1 as [h]:mm.

Aladin

========