Hello all ,
I have a rather complex forumula that was from a pre-existing sheet. I did not create the sheet. But it works very well.
Basically this formula counts all the Letters in a row I d like to modify it so it sums any numbers, and remove the weekday restriction
(We now have people on Saturday shifts)
<tbody>
</tbody>
Formula is in the LEAVE column
ATM it could count the letters we use to trace Holidays - H for Holiday
But we want to sum these now as we are tracking by hours . 7.5
=SUM(--NOT(ISNUMBER(IF(WEEKDAY(DATE($A$7,$A$8,OFFSET($A$10,0,31*($A$8-1)+1,1,31)),2)<=5,OFFSET(A13,0,31*($A$8-1)+1,1,31),0)))*--NOT(ISNUMBER(MATCH(DATE($A$7,$A$8,OFFSET($A$10,0,31*($A$8-1)+1,1,31)),Holidays!$B$2:$B$12,0))))+COUNT(OFFSET($A$10,0,31*($A$8-1)+1,1,31))-31
The sheets uses a form control scroll bar to slide from month to month,
and increase the month number in A8 , A7 contains the year.
The days and days of the week are then populated by another formula and a macro hids the relevant colums, Its a wonderfull sheet. But the formula is just a little to complex for me to edit
I have a rather complex forumula that was from a pre-existing sheet. I did not create the sheet. But it works very well.
Basically this formula counts all the Letters in a row I d like to modify it so it sums any numbers, and remove the weekday restriction
(We now have people on Saturday shifts)
01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | Leave | |
Name | Fri | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | |
BOB Smith | 7 | H | 0 |
<tbody>
</tbody>
Formula is in the LEAVE column
ATM it could count the letters we use to trace Holidays - H for Holiday
But we want to sum these now as we are tracking by hours . 7.5
=SUM(--NOT(ISNUMBER(IF(WEEKDAY(DATE($A$7,$A$8,OFFSET($A$10,0,31*($A$8-1)+1,1,31)),2)<=5,OFFSET(A13,0,31*($A$8-1)+1,1,31),0)))*--NOT(ISNUMBER(MATCH(DATE($A$7,$A$8,OFFSET($A$10,0,31*($A$8-1)+1,1,31)),Holidays!$B$2:$B$12,0))))+COUNT(OFFSET($A$10,0,31*($A$8-1)+1,1,31))-31
The sheets uses a form control scroll bar to slide from month to month,
and increase the month number in A8 , A7 contains the year.
The days and days of the week are then populated by another formula and a macro hids the relevant colums, Its a wonderfull sheet. But the formula is just a little to complex for me to edit