Add Times but Ignore everything else

[Dibble]

I have a sheet I need to do that will add up somebodies working hours.

The idea is that the staff can look at the sheet to check their forthcoming duties. To that end they need to be able to see 0700-1900. I have started by creating two columns for each day.. start time finish time. I have then in the next columm put a formula to calculate the total hours. This will ultimately be hidden to allow ease of viewing for the staff.

At the end of the row I want a total that will pull the information from the hidden cells (i.e 7 hidden cells for one week) and give me the total hours.

If somebody has a day off during this shift pattern then instead of a time entry it will simply show RD. This causes an expected error of #VALUE!

The RD bit could occur at any day of the week and so I need a formula that will look at all 7 hidden cells and return the total hours ignoring anything that is not a time.. i.e ignore all the #VALUE! cells.

Possible? Thanks in advance.

 

[barry houdini]

Which formula gives you value error? You can probably revise the formula to suppress that error.

E.g. if you have =B1-A1 to calculate time worked change to

=IF(ISERR(B1-A1),0,B1-A1)

Then you can calculate the weekly hours with a simple sum

 

[Dibble]

The formula is this

=IF(C4>D4,D4+1-C4,D4-C4)

This allows for shifts that go into the next day. e.g 1900 -0700

So I will try

=IF(ISERR(C4>D4,D4+1-C4,D4-C4))

I'll soon find out if that's wrong!

 

[barry houdini]

You could try this

=IF(ISERR(D4-C4),0,MOD(D4-C4,1))

 

[Dibble]

That cracked it.. thanks once again Barry. I'm indebted to you