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:

=IF(ISBLANK(I15),"",IF(HOUR(J15)=0,HOUR(J15)+24-HOUR
(I15),IF(HOUR(J15)>0,HOUR(J15)-HOUR(I15)))+(MINUTE(J15)
-MINUTE(I15))/60)-IF(HOUR(L15)=0,HOUR(L15)+24-HOUR(K15)
,IF(HOUR(L15)>0,HOUR(L15)-HOUR(K15)))+(MINUTE(L15)
-MINUTE(K15))/60

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.

Karen


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

Karen --

How about:

=IF(COUNT(I1:L1)>1,(COUNT(I1:J1)=2)*(J1+(J1<I1)-I1)+(COUNT(K1:L1)=2)*(L1+(L1<K1)-K1),"")

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

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

Aladin

=========