Tracking working hours on a schedule

Hello,

I have a manpower schedule that is used to track the working hours of our employees. The top row is the week# and underneath is the Month and under the month it is split into the days of the week.

I didn't make this original file but there is a sum formula that calculates all the hours up based on what you enter. For example:

Formula:

Please Login or Register  to view this content.

F19-IV19 is every day in the year. This equation would be for one employee for their Banked hours. the hours would add up and convert to days so if you typed in B+5 into November 6th it would add .63 days to your total banked hours.

Now the issue is that obviously this equation is long and tedious and the character limit has been reached. You can also type in B-5 to remove banked hours from the total but there is only enough room for the first 2 hours.

I'm hoping there is some way to shorten this formula to include any hours whether it be B-10 or B+10 (even though this would never happen) into maybe 1 line. ANY help would be appreciated! Thanks

Sorry if I am not clear enough. If anyone has a question I will try to answer it as best i can.

I am sure there is, but could you upload a sample workbook?
Also, if you wrapped code tags or function tags around that formula, it would make it easier to read !
(highlight the formula, then hit "#" or "fx" buttons on the edit window toolbar)

Self deleted, only works on first value in range...

Try an array formula like this for B6

=SUM(IF(LEFT(F6:IV6)="V",SUBSTITUTE(SUBSTITUTE(F6:IV6,"V",""),"+","")+0))

confirmed with CTRL+SHIFT+ENTER and copied down

and for "Banked hours" it would be a similar thing except with a division by 8 thrown in, i.e. this formula in E6

=SUM(IF(LEFT(F6:T6)="B",SUBSTITUTE(SUBSTITUTE(F6:T6,"B",""),"+","")+0))/8

Hey! Thanks for putting the Formula tags on the OP formula
What I finally came up with is this:
In B6 (this is an Array Formula, so use the formula bar to enter/copy , and Cntrl+Shift+Enter to enter, not just Enter):

Formula:

Please Login or Register  to view this content.

Drag down, and then across to D16 (or howevever many rows you need)
then select D6, drag to E6 , select E6, click the formula bar, and make this adjustment (again, needs to be entered using the Cntrl+Shift+Enter key combination, not just Enter):

Formula:

Please Login or Register  to view this content.

drag this down as far as needed

See (attached) Sheet3, sheet 1 is what you gave, sheet 2 is the interim calculations to get to...sheet 3, which shows these formulas (sheets 1 and 2 are not necessary, just there for comparison, and the formulas below were the way I arrived at these solutions, not necessary, just there to show how I arrived at these formulas)

Hope this helps