# Add Times but Ignore everything else

#### Dibble

##### New Member
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.

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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

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!

You could try this

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

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

Replies
1
Views
321
Replies
4
Views
353
Replies
6
Views
300
Replies
1
Views
240
Replies
6
Views
489

1,207,421
Messages
6,078,436
Members
446,337
Latest member
nrijkers

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back