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)
[TABLE="width: 1484"]
<tbody>[TR]
[TD][/TD]
[TD]01[/TD]
[TD]02[/TD]
[TD]03[/TD]
[TD]04[/TD]
[TD]05[/TD]
[TD]06[/TD]
[TD]07[/TD]
[TD]08[/TD]
[TD]09[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[TD]29[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]Leave[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[/TR]
[TR]
[TD]BOB Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]H[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
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)
[TABLE="width: 1484"]
<tbody>[TR]
[TD][/TD]
[TD]01[/TD]
[TD]02[/TD]
[TD]03[/TD]
[TD]04[/TD]
[TD]05[/TD]
[TD]06[/TD]
[TD]07[/TD]
[TD]08[/TD]
[TD]09[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[TD]29[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]Leave[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[/TR]
[TR]
[TD]BOB Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]H[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
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