Add Times but Ignore everything else

Dibble

New Member
Joined
Dec 21, 2003
Messages
25
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.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Dec 21, 2003
Messages
25
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,411
Messages
5,595,985
Members
414,035
Latest member
billbumkins

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top