MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Need help with calculating hours

Posted by Karen on January 04, 2002 9:07 AM

I have a timesheet on a spreadsheet in Excel 2000.
Column I is "In", Column J is "Out", Column K is "IN",
Column L is "Out" and Column M is where I want the
four columns totaled. Sometimes all four columns will
have time in them, and sometimes it will just be the
first two. This is the formula I tried to use, but
have had no success with it so far:


I'm not sure what I am doing wrong, but if the cells
in the first four columns are blank, I get a #Value
Error. If only the first two columns are completed,
for example Column I is 12 a and Column J is 8 a, then
it adds up to 32 hours. However, if all columns are
completed, for example Column K is 10 p and Column L
is 12 a, it adds the hours correctly.

If anyone could help me with this problem, I would
really appreciate it. A hearty thanks in advance to
any help that anyone could lend me.


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

Karen --

How about:


where M1 is cutom formatted as [h]:mm ?

Caveat. I'm assuming that there are no formulas in I:K that return a blank ("").