Formula help needed (Total hours worked but skip blank rows)

[Jaafar Tribak]

Hi all,

I have the following :


______A____ ____B___________ C

1___Time In______Time Out______Total Hours

2 ____10:00______14:00__________04:00

3 ____23:30______01:45__________02:15

4

5 ____13:00______14:30__________01:30


The Formula I have in Column C to calculate the total hours is : (filled down the rows below)
=IF(AND(A1="",B1=""),"",IF(AND(A1<>"",B1<>""),(1-A1)+B1,IF(A1>NOW(),0,(1-A1)+NOW())))

Then in cell H1, I am trying to get the sum of total hours as follows :

{=IF(C1:C4<>"",SUM(HOUR(C1:C4)+MINUTE(C1:C4)/60),0)} (Array Formula)

Cell H1 containing the array formula is is Number-formatted as General.

The array formula works well except if the cells in row 4 (ie both cells A4 AND B4) are blanks

In other words, If row 4 is blank as in the above table then the formula returns #VALUE ! error as it is trying to sum blank cells.

Can someone solve this without using a helping column ?

Thank you.

 

[James006]

Hello Jaafar,

In cell C2 ... you could have =MOD(B2-A2,1)

In cell H2 ... is following array formula of any help : =SUM(C2:C5)

Hope this will help

 

[Jaafar Tribak]

@James006

Sorry for the late reply.

I altered your suggestion slightly as follows :

In cell C1 .... =IF(MOD((1-A1)+B1;1)=0;"";MOD((1-A1)+B1;1))
In Cell H1 .... =HOUR((SUM(C1:C5)))+MINUTE(SUM(C1:C5))/60

This works and doesn't display the #VALUE error when a row is blank BUT Cell H1 gives the wrong result when the TimeOut is after midnight .

Any thoughts ?

Thank you.

 

[James006]

Hello Jaafar,

Just tested in cell H1 ... format as General ...

=HOUR(SUM(C1:C5))+MINUTE(SUM(C1:C5))/60

and everything is fine ...:wink:

 

[Jaafar Tribak]

Works like a charm !

I must have done something wrong when testing the last formula I posted as it is the same.

Anyway, thank you very much for your kind assistance.

 

[James006]

You are more than welcome ....