Formula Help

[bernie1973]

I am new to excel and self teaching, most likely doing it all the long way but every day is a learning day

I am attempting to extract information from worksheet 1 into worksheet 2.

Worksheet 1 is a record of employees and their hours worked on a daily basis for the full year.

1 employee in per row for 50 rows, 365 columns for each day of the year.

I am required to report back on a weekly basis the hours each individual has taken for annual leave, worked, overtime, sick leave etc

Below is an extract from worksheet 1 looks like

[TABLE="width: 444"]
<colgroup><col width="101" style="width: 76pt; mso-width-source: userset; mso-width-alt: 3693;"> <col width="30" style="width: 23pt; mso-width-source: userset; mso-width-alt: 1097;" span="16"> <tbody>[TR]
[TD="class: xl83, width: 101, bgcolor: transparent"] [/TD]
[TD="class: xl75, width: 30, bgcolor: #DDD9C4"]01[/TD]
[TD="class: xl75, width: 30, bgcolor: #DDD9C4"]02[/TD]
[TD="class: xl75, width: 30, bgcolor: #DDD9C4"]03[/TD]
[TD="class: xl75, width: 30, bgcolor: #DDD9C4"]04[/TD]
[TD="class: xl75, width: 30, bgcolor: #DDD9C4"]05[/TD]
[TD="class: xl75, width: 30, bgcolor: #DDD9C4"]06[/TD]
[TD="class: xl75, width: 30, bgcolor: #DDD9C4"]07[/TD]
[TD="class: xl75, width: 30, bgcolor: #DDD9C4"]08[/TD]
[TD="class: xl75, width: 30, bgcolor: #DDD9C4"]09[/TD]
[TD="class: xl75, width: 30, bgcolor: #DDD9C4"]10[/TD]
[TD="class: xl75, width: 30, bgcolor: #DDD9C4"]11[/TD]
[TD="class: xl75, width: 30, bgcolor: #DDD9C4"]12[/TD]
[TD="class: xl75, width: 30, bgcolor: #DDD9C4"]13[/TD]
[TD="class: xl75, width: 30, bgcolor: #DDD9C4"]14[/TD]
[TD="class: xl75, width: 30, bgcolor: #DDD9C4"]15[/TD]
[TD="class: xl75, width: 30, bgcolor: #DDD9C4"]16[/TD]
[/TR]
[TR]
[TD="class: xl84, bgcolor: #DDD9C4"] [/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Sun[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Mon[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Tue[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Wed[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Thu[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Fri[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Sat[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Sun[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Mon[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Tue[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Wed[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Thu[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Fri[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Sat[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Sun[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Mon[/TD]
[/TR]
[TR]
[TD="class: xl81, width: 101, bgcolor: #EEECE1"]Employee 1[/TD]
[TD="class: xl86"] [/TD]
[TD="class: xl87"]d[/TD]
[TD="class: xl87"]d[/TD]
[TD="class: xl87"]n[/TD]
[TD="class: xl87"]n[/TD]
[TD="class: xl87, bgcolor: #EEECE1"] [/TD]
[TD="class: xl87, bgcolor: #EEECE1"] [/TD]
[TD="class: xl87, bgcolor: #EEECE1"] [/TD]
[TD="class: xl87, bgcolor: #EEECE1"] [/TD]
[TD="class: xl87, bgcolor: #EEECE1"] [/TD]
[TD="class: xl87"]d[/TD]
[TD="class: xl87"]d[/TD]
[TD="class: xl87"]n[/TD]
[TD="class: xl87"]n[/TD]
[TD="class: xl87"]n[/TD]
[TD="class: xl86, bgcolor: #EEECE1"] [/TD]
[/TR]
[TR]
[TD="class: xl81, width: 101, bgcolor: #EEECE1"]Employee 2[/TD]
[TD="class: xl79"] [/TD]
[TD="class: xl80"]d[/TD]
[TD="class: xl80, bgcolor: transparent"]d[/TD]
[TD="class: xl80, bgcolor: transparent"]n[/TD]
[TD="class: xl80, bgcolor: transparent"]n[/TD]
[TD="class: xl80, bgcolor: transparent"] [/TD]
[TD="class: xl80, bgcolor: transparent"] [/TD]
[TD="class: xl80, bgcolor: transparent"] [/TD]
[TD="class: xl80, bgcolor: transparent"] [/TD]
[TD="class: xl80, bgcolor: transparent"] [/TD]
[TD="class: xl80, bgcolor: transparent"]d[/TD]
[TD="class: xl80, bgcolor: transparent"]d[/TD]
[TD="class: xl80, bgcolor: transparent"]n[/TD]
[TD="class: xl80, bgcolor: transparent"]n[/TD]
[TD="class: xl80, bgcolor: transparent"]n[/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl81, width: 101, bgcolor: #EEECE1"]Employee 3[/TD]
[TD="class: xl86"] [/TD]
[TD="class: xl87"]d[/TD]
[TD="class: xl87"]d[/TD]
[TD="class: xl87"]n[/TD]
[TD="class: xl87"]n[/TD]
[TD="class: xl87, bgcolor: #EEECE1"] [/TD]
[TD="class: xl87, bgcolor: #EEECE1"] [/TD]
[TD="class: xl87, bgcolor: #EEECE1"] [/TD]
[TD="class: xl87, bgcolor: #EEECE1"] [/TD]
[TD="class: xl87, bgcolor: #EEECE1"] [/TD]
[TD="class: xl87"]d[/TD]
[TD="class: xl87"]d[/TD]
[TD="class: xl87"]n[/TD]
[TD="class: xl87"]n[/TD]
[TD="class: xl87"]n[/TD]
[TD="class: xl86, bgcolor: #EEECE1"] [/TD]
[/TR]
[TR]
[TD="class: xl81, width: 101, bgcolor: #EEECE1"]Employee 4[/TD]
[TD="class: xl79"] [/TD]
[TD="class: xl80"]d[/TD]
[TD="class: xl80, bgcolor: transparent"]d[/TD]
[TD="class: xl80, bgcolor: transparent"]n[/TD]
[TD="class: xl80, bgcolor: transparent"]n[/TD]
[TD="class: xl80, bgcolor: transparent"] [/TD]
[TD="class: xl80, bgcolor: transparent"] [/TD]
[TD="class: xl80, bgcolor: transparent"] [/TD]
[TD="class: xl80, bgcolor: transparent"] [/TD]
[TD="class: xl80, bgcolor: transparent"] [/TD]
[TD="class: xl80, bgcolor: transparent"]d[/TD]
[TD="class: xl80, bgcolor: transparent"]d[/TD]
[TD="class: xl80, bgcolor: transparent"]n[/TD]
[TD="class: xl80, bgcolor: transparent"]n[/TD]
[TD="class: xl80, bgcolor: transparent"]n[/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl81, width: 101, bgcolor: #EEECE1"]Employee 5[/TD]
[TD="class: xl86"] [/TD]
[TD="class: xl87"]d[/TD]
[TD="class: xl87"]d[/TD]
[TD="class: xl87"]n[/TD]
[TD="class: xl87"]n[/TD]
[TD="class: xl87, bgcolor: #EEECE1"] [/TD]
[TD="class: xl87, bgcolor: #EEECE1"] [/TD]
[TD="class: xl87, bgcolor: #EEECE1"] [/TD]
[TD="class: xl87, bgcolor: #EEECE1"] [/TD]
[TD="class: xl87, bgcolor: #EEECE1"] [/TD]
[TD="class: xl87"]d[/TD]
[TD="class: xl87"]d[/TD]
[TD="class: xl87"]n[/TD]
[TD="class: xl87"]n[/TD]
[TD="class: xl87"]n[/TD]
[TD="class: xl86, bgcolor: #EEECE1"] [/TD]
[/TR]
[TR]
[TD="class: xl81, width: 101, bgcolor: #EEECE1"]Employee 6[/TD]
[TD="class: xl79"] [/TD]
[TD="class: xl80"]d[/TD]
[TD="class: xl80, bgcolor: transparent"]d[/TD]
[TD="class: xl80, bgcolor: transparent"]n[/TD]
[TD="class: xl80, bgcolor: transparent"]n[/TD]
[TD="class: xl80, bgcolor: transparent"] [/TD]
[TD="class: xl80, bgcolor: transparent"] [/TD]
[TD="class: xl80, bgcolor: transparent"] [/TD]
[TD="class: xl80, bgcolor: transparent"] [/TD]
[TD="class: xl80, bgcolor: transparent"] [/TD]
[TD="class: xl80, bgcolor: transparent"]d[/TD]
[TD="class: xl80, bgcolor: transparent"]d[/TD]
[TD="class: xl80, bgcolor: transparent"]n[/TD]
[TD="class: xl80, bgcolor: transparent"]n[/TD]
[TD="class: xl80, bgcolor: transparent"]n[/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl81, width: 101, bgcolor: #EEECE1"]Employee 7[/TD]
[TD="class: xl86"] [/TD]
[TD="class: xl87"]d[/TD]
[TD="class: xl87"]d[/TD]
[TD="class: xl87"]n[/TD]
[TD="class: xl87"]n[/TD]
[TD="class: xl87, bgcolor: #EEECE1"] [/TD]
[TD="class: xl87, bgcolor: #EEECE1"] [/TD]
[TD="class: xl87, bgcolor: #EEECE1"] [/TD]
[TD="class: xl87, bgcolor: #EEECE1"] [/TD]
[TD="class: xl87, bgcolor: #EEECE1"] [/TD]
[TD="class: xl87"]d[/TD]
[TD="class: xl87"]d[/TD]
[TD="class: xl87"]n[/TD]
[TD="class: xl87"]n[/TD]
[TD="class: xl87"]n[/TD]
[TD="class: xl86, bgcolor: #EEECE1"] [/TD]
[/TR]
[TR]
[TD="class: xl81, width: 101, bgcolor: #EEECE1"]Employee 8[/TD]
[TD="class: xl79"] [/TD]
[TD="class: xl80"]d[/TD]
[TD="class: xl80, bgcolor: transparent"]d[/TD]
[TD="class: xl80, bgcolor: transparent"]n[/TD]
[TD="class: xl80, bgcolor: transparent"]n[/TD]
[TD="class: xl80, bgcolor: transparent"] [/TD]
[TD="class: xl80, bgcolor: transparent"] [/TD]
[TD="class: xl80, bgcolor: transparent"] [/TD]
[TD="class: xl80, bgcolor: transparent"] [/TD]
[TD="class: xl80, bgcolor: transparent"] [/TD]
[TD="class: xl80, bgcolor: transparent"]d[/TD]
[TD="class: xl80, bgcolor: transparent"]d[/TD]
[TD="class: xl80, bgcolor: transparent"]n[/TD]
[TD="class: xl80, bgcolor: transparent"]n[/TD]
[TD="class: xl80, bgcolor: transparent"]n[/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

Worksheet 2 layout:

[TABLE="width: 239"]
<colgroup><col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;"> <col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;" span="4"> <tbody>[TR]
[TD="class: xl77, width: 47, bgcolor: #FCD5B4"]Week No[/TD]
[TD="class: xl78, width: 68, bgcolor: #FCD5B4"]Week Start[/TD]
[TD="class: xl78, width: 68, bgcolor: #FCD5B4"]Week Ending[/TD]
[TD="class: xl76, width: 68, bgcolor: #E6B8B7"]Annual Leave (A)[/TD]
[TD="class: xl75, width: 68, bgcolor: #E6B8B7"]Over time (OT)[/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: #FCD5B4"]1[/TD]
[TD="class: xl80, bgcolor: #DCE6F1"]01 Jan[/TD]
[TD="class: xl80, bgcolor: #DCE6F1"]07 Jan[/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: #FCD5B4"]2[/TD]
[TD="class: xl80, bgcolor: #DCE6F1"]08 Jan[/TD]
[TD="class: xl80, bgcolor: #DCE6F1"]14 Jan[/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: #FCD5B4"]3[/TD]
[TD="class: xl80, bgcolor: #DCE6F1"]15 Jan[/TD]
[TD="class: xl80, bgcolor: #DCE6F1"]21 Jan[/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: #FCD5B4"]4[/TD]
[TD="class: xl80, bgcolor: #DCE6F1"]22 Jan[/TD]
[TD="class: xl80, bgcolor: #DCE6F1"]28 Jan[/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: #FCD5B4"]5[/TD]
[TD="class: xl80, bgcolor: #DCE6F1"]29 Jan[/TD]
[TD="class: xl80, bgcolor: #DCE6F1"]04 Feb[/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: #FCD5B4"]6[/TD]
[TD="class: xl80, bgcolor: #DCE6F1"]05 Feb[/TD]
[TD="class: xl80, bgcolor: #DCE6F1"]11 Feb[/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: #FCD5B4"]7[/TD]
[TD="class: xl80, bgcolor: #DCE6F1"]12 Feb[/TD]
[TD="class: xl80, bgcolor: #DCE6F1"]18 Feb[/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: #FCD5B4"]8[/TD]
[TD="class: xl80, bgcolor: #DCE6F1"]19 Feb[/TD]
[TD="class: xl80, bgcolor: #DCE6F1"]25 Feb[/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: #FCD5B4"]9[/TD]
[TD="class: xl80, bgcolor: #DCE6F1"]26 Feb[/TD]
[TD="class: xl80, bgcolor: #DCE6F1"]04 Mar[/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[TD="class: xl81, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]


The formula I am looking for is to be inserted into worksheet 2, from the information be ascertained from worksheet 1 so for calculating for total annual leave at week 1 for all employees the range would be B8:H51, with the permutations to count being a = 12 hours, a11 = 11 hours, a10 = 10hours leave taken all the way down to a1 where only 1 hour of leave has been taken.

Hopefully I have explained it good enough, if not please get back in touch.

 

[James006]

Hello,

For a start ... in Sheet2, you could test in cell D2

=SUMPRODUCT(--(INDIRECT("Sheet1!"&ADDRESS(8,DAY($B2)+1,4)&":"&ADDRESS(51,DAY($C2)+1,4))="d"))

This formula will work the number of "d" ...(there are no "a" in your sample...) in sheet 1 in your range B8:H51 ... for week 1 ... to be copied down ...

Hope this will help

 

[bernie1973]

Hi James,006

Apologies in missing out the "a" references as I lost focus, below is a section from February with a's added in, changing the "d" to an "a" as per your formula works ideal, however the added problem I have is that sometimes employees don't always take a full shift off and just part of the shift, they must take annual leave to the full hour, therefore I have 12 options so for annual leave a a11, a10,a9,a8,a7,a6,a5,a4,a3,a2,a1. where the a prefix denotes annual and the number accounts for the hours taken "a" means a 12 hours shift taken off on annual leave

To complicate the sums for the week, I would require to have a formula that can account/sum for the 12 x a permutations, hope you can resolve and many thanks again

[TABLE="width: 400"]
<colgroup><col width="26" style="width: 20pt; mso-width-source: userset; mso-width-alt: 950;" span="20"><tbody>[TR]
[TD="class: xl75, width: 26, bgcolor: #DDD9C4"]01[/TD]
[TD="class: xl75, width: 26, bgcolor: #DDD9C4"]02[/TD]
[TD="class: xl75, width: 26, bgcolor: #DDD9C4"]03[/TD]
[TD="class: xl75, width: 26, bgcolor: #DDD9C4"]04[/TD]
[TD="class: xl75, width: 26, bgcolor: #DDD9C4"]05[/TD]
[TD="class: xl75, width: 26, bgcolor: #DDD9C4"]06[/TD]
[TD="class: xl75, width: 26, bgcolor: #DDD9C4"]07[/TD]
[TD="class: xl75, width: 26, bgcolor: #DDD9C4"]08[/TD]
[TD="class: xl75, width: 26, bgcolor: #DDD9C4"]09[/TD]
[TD="class: xl75, width: 26, bgcolor: #DDD9C4"]10[/TD]
[TD="class: xl75, width: 26, bgcolor: #DDD9C4"]11[/TD]
[TD="class: xl75, width: 26, bgcolor: #DDD9C4"]12[/TD]
[TD="class: xl75, width: 26, bgcolor: #DDD9C4"]13[/TD]
[TD="class: xl75, width: 26, bgcolor: #DDD9C4"]14[/TD]
[TD="class: xl75, width: 26, bgcolor: #DDD9C4"]15[/TD]
[TD="class: xl75, width: 26, bgcolor: #DDD9C4"]16[/TD]
[TD="class: xl75, width: 26, bgcolor: #DDD9C4"]17[/TD]
[TD="class: xl75, width: 26, bgcolor: #DDD9C4"]18[/TD]
[TD="class: xl75, width: 26, bgcolor: #DDD9C4"]19[/TD]
[TD="class: xl75, width: 26, bgcolor: #DDD9C4"]20[/TD]
[/TR]
[TR]
[TD="class: xl76, bgcolor: #DDD9C4"]Wed[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Thu[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Fri[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Sat[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Sun[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Mon[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Tue[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Wed[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Thu[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Fri[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Sat[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Sun[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Mon[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Tue[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Wed[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Thu[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Fri[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Sat[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Sun[/TD]
[TD="class: xl76, bgcolor: #DDD9C4"]Mon[/TD]
[/TR]
[TR]
[TD="class: xl81"]n[/TD]
[TD="class: xl81"]n[/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl81, bgcolor: #EEECE1"] [/TD]
[TD="class: xl81, bgcolor: #EEECE1"] [/TD]
[TD="class: xl81"]d[/TD]
[TD="class: xl81"]d[/TD]
[TD="class: xl81"]n[/TD]
[TD="class: xl81"]n[/TD]
[TD="class: xl81"]n[/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl81, bgcolor: #EEECE1"]OT[/TD]
[TD="class: xl81"]d[/TD]
[TD="class: xl81"]d[/TD]
[TD="class: xl81"]n[/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"]d[/TD]
[TD="class: xl79, bgcolor: transparent"]d[/TD]
[TD="class: xl79"]a2[/TD]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"]T[/TD]
[TD="class: xl79, bgcolor: transparent"]R[/TD]
[TD="class: xl79, bgcolor: transparent"]R[/TD]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[/TR]
[TR]
[TD="class: xl81"]n[/TD]
[TD="class: xl81"]n[/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl81, bgcolor: #EEECE1"] [/TD]
[TD="class: xl81, bgcolor: #EEECE1"] [/TD]
[TD="class: xl81"]d[/TD]
[TD="class: xl81"]d[/TD]
[TD="class: xl81"]n[/TD]
[TD="class: xl81"]n[/TD]
[TD="class: xl81"]n[/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl81"]d[/TD]
[TD="class: xl81"]d[/TD]
[TD="class: xl81"]d[/TD]
[TD="class: xl81"]n[/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"]d[/TD]
[TD="class: xl79"]a6[/TD]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"]d[/TD]
[TD="class: xl79, bgcolor: transparent"]d[/TD]
[TD="class: xl79, bgcolor: transparent"]d[/TD]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[/TR]
[TR]
[TD="class: xl81"]n[/TD]
[TD="class: xl81"]n[/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl81, bgcolor: #EEECE1"] [/TD]
[TD="class: xl81, bgcolor: #EEECE1"] [/TD]
[TD="class: xl81"]a8[/TD]
[TD="class: xl81"]d[/TD]
[TD="class: xl81"]n[/TD]
[TD="class: xl81"]n[/TD]
[TD="class: xl81"]n[/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl81"]d[/TD]
[TD="class: xl81, bgcolor: #EEECE1"]COW[/TD]
[TD="class: xl81, bgcolor: #EEECE1"]COW[/TD]
[TD="class: xl81"]n[/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"]d[/TD]
[TD="class: xl79, bgcolor: transparent"]d[/TD]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"]d[/TD]
[TD="class: xl79, bgcolor: transparent"]d[/TD]
[TD="class: xl79, bgcolor: transparent"]d[/TD]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[/TR]
[TR]
[TD="class: xl81"]n[/TD]
[TD="class: xl81"]n[/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl81, bgcolor: #EEECE1"] [/TD]
[TD="class: xl81, bgcolor: #EEECE1"] [/TD]
[TD="class: xl81"]d[/TD]
[TD="class: xl81"]d[/TD]
[TD="class: xl81"]n[/TD]
[TD="class: xl81"]n[/TD]
[TD="class: xl81"]n[/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl81"]d[/TD]
[TD="class: xl81"]d[/TD]
[TD="class: xl81"]d[/TD]
[TD="class: xl81"]n[/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"]rr[/TD]
[TD="class: xl79"]A[/TD]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"]d[/TD]
[TD="class: xl79, bgcolor: transparent"]d[/TD]
[TD="class: xl79, bgcolor: transparent"]d[/TD]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[/TR]
[TR]
[TD="class: xl81"]n[/TD]
[TD="class: xl81"]n[/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl81, bgcolor: #EEECE1"] [/TD]
[TD="class: xl81, bgcolor: #EEECE1"] [/TD]
[TD="class: xl81"]d[/TD]
[TD="class: xl81, bgcolor: #EEECE1"]rr[/TD]
[TD="class: xl81"]n[/TD]
[TD="class: xl81"]n[/TD]
[TD="class: xl81"]n[/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl80, bgcolor: #EEECE1"] [/TD]
[TD="class: xl81"]A[/TD]
[TD="class: xl81"]A[/TD]
[TD="class: xl81"]A[/TD]
[TD="class: xl81"]n[/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"]d[/TD]
[TD="class: xl79, bgcolor: transparent"]d[/TD]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl78, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"]d[/TD]
[TD="class: xl79, bgcolor: transparent"]d[/TD]
[TD="class: xl79, bgcolor: transparent"]d[/TD]
[TD="class: xl79, bgcolor: transparent"]n[/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hi,

Still for Sheet 2 ... in cell D2 ... a second attempt :

=SUMPRODUCT(--(ISNUMBER(SEARCH("A",INDIRECT("Sheet1!"&ADDRESS(8,DAY($B2)+1,4)&":"&ADDRESS(51,DAY($C2)+1,4)),1))))

Hope this will help

 

[bernie1973]

Hi James,<o></o>
Thanks again for the formula, we are almost there and I thankyou for your patience, the formula is not capturing all A’s within its addressrange ie its picking up 2 out of say 14 for a week range of cells, I can’t seewhere/how it is doing this <o></o>
<v:shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"> <v:f eqn="sum @0 1 0"> <v:f eqn="sum 0 0 @1"> <v:f eqn="prod @2 1 2"> <v:f eqn="prod @3 21600 pixelWidth"> <v:f eqn="prod @3 21600 pixelHeight"> <v:f eqn="sum @0 0 1"> <v:f eqn="prod @6 1 2"> <v:f eqn="prod @7 21600 pixelWidth"> <v:f eqn="sum @8 21600 0"> <v:f eqn="prod @7 21600 pixelHeight"> <v:f eqn="sum @10 21600 0"> </v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas> <vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"> <o:lock aspectratio="t" v:ext="edit"></o:lock></vath></v:stroke></v:shapetype><v:shape id="Picture_x0020_2" style="width: 366.75pt; height: 308.25pt; visibility: visible; mso-wrap-style: square;" type="#_x0000_t75" o:spid="_x0000_i1025"> <v:imagedata o:title="" src="file:///C:\Users\DunlopB\AppData\Local\Temp\msohtmlclip1\01\clip_image001.png"></v:imagedata></v:shape><o></o>
In Sheet 2 I have:<o></o>
[TABLE="class: MsoNormalTable, width: 239"]
<tbody>[TR]
[TD="width: 47"]

Week No<o></o>​

[/TD]
[TD="width: 68"]

Week Start<o></o>​

[/TD]
[TD="width: 68"]

Week Ending<o></o>​

[/TD]
[TD="width: 68"]

Annual Leave (A)<o></o>​

[/TD]
[TD="width: 68"]

Countif<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 47"]

1<o></o>​

[/TD]
[TD="width: 68"]

01 Jan<o></o>​

[/TD]
[TD="width: 68"]

07 Jan<o></o>​

[/TD]
[TD="width: 68, bgcolor: transparent"]

2<o></o>​

[/TD]
[TD="width: 68, bgcolor: transparent"]

<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 47"]

2<o></o>​

[/TD]
[TD="width: 68"]

08 Jan<o></o>​

[/TD]
[TD="width: 68"]

14 Jan<o></o>​

[/TD]
[TD="width: 68, bgcolor: transparent"]

0<o></o>​

[/TD]
[TD="width: 68, bgcolor: transparent"]

<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 47"]

3<o></o>​

[/TD]
[TD="width: 68"]

15 Jan<o></o>​

[/TD]
[TD="width: 68"]

21 Jan<o></o>​

[/TD]
[TD="width: 68, bgcolor: transparent"]

2<o></o>​

[/TD]
[TD="width: 68, bgcolor: transparent"]

<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 47"]

4<o></o>​

[/TD]
[TD="width: 68"]

22 Jan<o></o>​

[/TD]
[TD="width: 68"]

28 Jan<o></o>​

[/TD]
[TD="width: 68, bgcolor: transparent"]

0<o></o>​

[/TD]
[TD="width: 68, bgcolor: transparent"]

<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 47"]

5<o></o>​

[/TD]
[TD="width: 68"]

29 Jan<o></o>​

[/TD]
[TD="width: 68"]

04 Feb<o></o>​

[/TD]
[TD="width: 68, bgcolor: transparent"]

4<o></o>​

[/TD]
[TD="width: 68, bgcolor: transparent"]

<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 47"]

6<o></o>​

[/TD]
[TD="width: 68"]

05 Feb<o></o>​

[/TD]
[TD="width: 68"]

11 Feb<o></o>​

[/TD]
[TD="width: 68, bgcolor: transparent"]

2<o></o>​

[/TD]
[TD="width: 68, bgcolor: transparent"]

134.75<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 47"]

7<o></o>​

[/TD]
[TD="width: 68"]

12 Feb<o></o>​

[/TD]
[TD="width: 68"]

18 Feb<o></o>​

[/TD]
[TD="width: 68, bgcolor: transparent"]

2<o></o>​

[/TD]
[TD="width: 68, bgcolor: transparent"]

208.25<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 47"]

8<o></o>​

[/TD]
[TD="width: 68"]

19 Feb<o></o>​

[/TD]
[TD="width: 68"]

25 Feb<o></o>​

[/TD]
[TD="width: 68, bgcolor: transparent"]

0<o></o>​

[/TD]
[TD="width: 68, bgcolor: transparent"]

159<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 47"]

9<o></o>​

[/TD]
[TD="width: 68"]

26 Feb<o></o>​

[/TD]
[TD="width: 68"]

04 Mar<o></o>​

[/TD]
[TD="width: 68, bgcolor: transparent"]

4<o></o>​

[/TD]
[TD="width: 68, bgcolor: transparent"]

0<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 47"]

10<o></o>​

[/TD]
[TD="width: 68"]

05 Mar<o></o>​

[/TD]
[TD="width: 68"]

11 Mar<o></o>​

[/TD]
[TD="width: 68, bgcolor: transparent"]

2<o></o>​

[/TD]
[TD="width: 68, bgcolor: transparent"]

0<o></o>​

[/TD]
[/TR]
[TR]
[TD="width: 47"]

11<o></o>​

[/TD]
[TD="width: 68"]

12 Mar<o></o>​

[/TD]
[TD="width: 68"]

18 Mar<o></o>​

[/TD]
[TD="width: 68, bgcolor: transparent"]

2<o></o>​

[/TD]
[TD="width: 68, bgcolor: transparent"]

0<o></o>​

[/TD]
[/TR]
</tbody>[/TABLE]
<o> </o>
The Annual Leave column is using your formula – please notethere is no data inputted yet for weeks 9,10 and 11 and its showing a return,for the countif column – this is where I have added my long countif formula – Ihave not populated it for weeks 1 to 5, this is returning in hours as previously communicated
Below is a very long “countif” formula summing what I amlooking for result wise but I have to manually change for each and every newrow/cell sun to sat week range<o></o>
=COUNTIF('Sheet1'!$BP$8:$BV$51,"a")*12.25+COUNTIF('Sheet1'!$BP$8:$BV$51,"a11")*11+COUNTIF('Sheet1'!$BP$8:$BV$51,"a10")*10+COUNTIF('Sheet1’!$BP$8:$BV$51,"a9")*9+COUNTIF('Sheet1'!$BP$8:$BV$51,"a8")*8+COUNTIF('Sheet1'!$BP$8:$BV$51,"a7")*7+COUNTIF('Sheet1'!$BP$8:$BV$51,"a6.25")*6.25+COUNTIF('Sheet1'!$BP$8:$BV$51,"a6")*6+COUNTIF('2017'!$BP$8:$BV$51,"a5")*5+COUNTIF('Sheet1'!$BP$8:$BV$51,"a4")*4+COUNTIF('Sheet1'!$BP$8:$BV$51,"a3")*3+COUNTIF('Sheet1'!$BP$8:$BV$51,"a2")*2+COUNTIF('Sheet1'!$BP$8:$BV$51,"a1")*1<o></o>
Again any help to simplify would be brilliant – or is therea way I can attach the excel workbook rather than screen shots?<o></o>

 

[oldbrewer]

[TABLE="width: 1378"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]01/01/2017[/TD]
[TD]02/01/2017[/TD]
[TD]03/01/2017[/TD]
[TD]04/01/2017[/TD]
[TD]05/01/2017[/TD]
[TD]06/01/2017[/TD]
[TD]07/01/2017[/TD]
[TD]08/01/2017[/TD]
[TD]09/01/2017[/TD]
[TD]10/01/2017[/TD]
[TD]11/01/2017[/TD]
[TD]12/01/2017[/TD]
[TD]13/01/2017[/TD]
[TD]14/01/2017[/TD]
[TD]15/01/2017[/TD]
[TD]16/01/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/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][/TD]
[/TR]
[TR]
[TD]Employee 1[/TD]
[TD]worked normal[/TD]
[TD] [/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]worked overtime[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3[/TD]
[TD] [/TD]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]annual leave[/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]
[/TR]
[TR]
[TD][/TD]
[TD]sick leave[/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]
[/TR]
[TR]
[TD]Employee 2[/TD]
[TD]worked normal[/TD]
[TD] [/TD]
[TD]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]worked overtime[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]annual leave[/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]
[/TR]
[TR]
[TD][/TD]
[TD]sick leave[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/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]
[/TR]
[TR]
[TD]Employee 3[/TD]
[TD]worked normal[/TD]
[TD] [/TD]
[TD]8[/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]worked overtime[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]annual leave[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]4[/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]
[/TR]
[TR]
[TD][/TD]
[TD]sick leave[/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]
[/TR]
[TR]
[TD]Employee 4[/TD]
[TD]worked normal[/TD]
[TD] [/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD] [/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]worked overtime[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]annual leave[/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]
[/TR]
[TR]
[TD][/TD]
[TD]sick leave[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]8[/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]
[/TR]
[TR]
[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]
[/TR]
[TR]
[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]
[/TR]
[TR]
[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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]01/01/2017[/TD]
[TD="colspan: 4"]08/01/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]07/01/2017[/TD]
[TD="colspan: 4"]14/01/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]worked normal[/TD]
[TD]worked overtime[/TD]
[TD]annual leave[/TD]
[TD]sick leave[/TD]
[TD]worked normal[/TD]
[TD]worked overtime[/TD]
[TD]annual leave[/TD]
[TD]sick leave[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Employee1[/TD]
[TD]49[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]40[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Employee2[/TD]
[TD]24[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]24[/TD]
[TD]40[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Employee3[/TD]
[TD]44[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]40[/TD]
[TD]6[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Employee4[/TD]
[TD]40[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]8[/TD]
[TD]40[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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]
[/TR]
[TR]
[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]
[/TR]
[TR]
[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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]as you have weeks/ employees and 4 time categories[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]the above is the first step[/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]
[/TR]
[TR]
[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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]from that you can pull employee with most sick leave, overtime worked etc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]the formula giving employee1 first week = 49 is[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]=SUMPRODUCT(($C$1:$R$1>=$G$22)*($C$1:$R$1<=$G$23)*($C3:$R3))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]I copied this down and across then had to make manual adjustments to formulas[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]if this approach is acceptable we could try and make a single formula universal[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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]
[/TR]
[TR]
[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]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

I am required to report back on a weekly basis the hours EACH INDIVIDUAL has taken for annual leave, worked, overtime, sick leave etc

do you want for each week or for each employee - I am confused (easily)

 

[bernie1973]

I require for a weekly total for all employees on annual leave, worked, overtime, sick leave, special leave etc, that is accessed by the director for his reports all on sheet 2

On a monthly / annual basis for each employee for their annual leave, worked, overtime, sick leave, special leave etc this is in-house for our own record keeping/tracking all on sheet 1

Sheet 1 is working fine, its the recording on Sheet 2 for each week (Sun to Sat) is the headache, firstly to distinguish from the 365 columns the dates for each week No and then extracting the 7 x 51 cells with different codes, a prefix for annual, d for dayshift, n for nightshift, OT for overtime, SCS for self certified sick, then NO, SPL, CS, UD and a couple more

These codes if just the letters are calculated at 12 hours, however, staff may have a split day where they have 7 hours annual and 5 hours TOIL where we record the 7 hours annual as a7 and the TOIL as TO5.

Hoping the above clarifies the muddy water I think I have created

Cheers

 

[James006]

Hi James,<o></o>
Thanks again for the formula, we are almost there and I thankyou for your patience, the formula is not capturing all A’s within its addressrange ie its picking up 2 out of say 14 for a week range of cells, I can’t seewhere/how it is doing this <o></o>

Hi again,

The search function is case sensitive ... and does not consider "A" as identical to "a" ...

Another attempt for cell D2 :

=SUMPRODUCT(--(LEFT(INDIRECT("Sheet1!"&ADDRESS(3,DAY($B2)+1,4)&":"&ADDRESS(51,DAY($C2)+1,4)),1)="a"))

Hope this will help

 

[oldbrewer]

[TABLE="width: 1209"]
<colgroup><col><col><col span="2"><col><col><col><col span="2"><col span="26"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]01/01/2017[/TD]
[TD]02/01/2017[/TD]
[TD]03/01/2017[/TD]
[TD]04/01/2017[/TD]
[TD]05/01/2017[/TD]
[TD]06/01/2017[/TD]
[TD]07/01/2017[/TD]
[TD]08/01/2017[/TD]
[TD]09/01/2017[/TD]
[TD]10/01/2017[/TD]
[TD]11/01/2017[/TD]
[TD]12/01/2017[/TD]
[TD]13/01/2017[/TD]
[TD]14/01/2017[/TD]
[TD]15/01/2017[/TD]
[TD]16/01/2017[/TD]
[TD]17/01/2017[/TD]
[TD]18/01/2017[/TD]
[TD]19/01/2017[/TD]
[TD]20/01/2017[/TD]
[TD]21/01/2017[/TD]
[TD]22/01/2017[/TD]
[TD]23/01/2017[/TD]
[TD]24/01/2017[/TD]
[TD]25/01/2017[/TD]
[TD]26/01/2017[/TD]
[TD]27/01/2017[/TD]
[TD]28/01/2017[/TD]
[TD]29/01/2017[/TD]
[TD]30/01/2017[/TD]
[TD]31/01/2017[/TD]
[TD]01/02/2017[/TD]
[TD]02/02/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]emp1[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]emp2[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]S[/TD]
[TD]S[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]emp3[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W15[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]emp4[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]emp5[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]T8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]emp6[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]L[/TD]
[TD]L[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]emp7[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]emp8[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]S[/TD]
[TD]S[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]emp9[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W15[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]emp10[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]emp11[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]T8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]emp12[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]L6[/TD]
[TD]L6[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]emp13[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]emp14[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]T8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]emp15[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]L[/TD]
[TD]L[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]emp16[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W6[/TD]
[TD]W6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD]W[/TD]
[TD][/TD]
[/TR]
[TR]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]W14[/TD]
[TD="colspan: 7"]12 HOURS WORKED_TOT HOURS[/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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]S[/TD]
[TD="colspan: 2"]SICK[/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][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD="colspan: 4"]ANNUAL LEAVE[/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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]T4[/TD]
[TD="colspan: 6"]TIME OFF IN LIEU_HOURS[/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]
[/TR]
[TR]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]L[/TD]
[TD="colspan: 4"]SPECIAL LEAVE[/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]
[/TR]
[TR]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/01/2017[/TD]
[TD="align: right"]08/01/2017[/TD]
[TD="align: right"]15/01/2017[/TD]
[TD="align: right"]22/01/2017[/TD]
[TD="align: right"]29/01/2017[/TD]
[TD="align: right"]05/02/2017[/TD]
[TD="align: right"]12/02/2017[/TD]
[TD="align: right"]19/02/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 11"]this macro has looked at the data for first week (for test purposes)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]07/01/2017[/TD]
[TD="align: right"]14/01/2017[/TD]
[TD="align: right"]21/01/2017[/TD]
[TD="align: right"]28/01/2017[/TD]
[TD="align: right"]04/02/2017[/TD]
[TD="align: right"]11/02/2017[/TD]
[TD="align: right"]18/02/2017[/TD]
[TD="align: right"]25/02/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 10"]and produced the lower table (S=1 day) (annual leave in days)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]WORKED[/TD]
[TD][/TD]
[TD="align: right"]612[/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][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]OVERTIME[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]For j = 3 To 9[/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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SICK[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"] For k = 3 To 18[/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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]ANNUAL LEAVE[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"] temp = Cells(k, j)[/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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]LIEU TIME[/TD]
[TD][/TD]
[TD="align: right"]66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"] suff = Mid(Cells(k, j), 2, 2)[/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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]SPEC LEAVE[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"] Lenn = Len(Cells(k, j))[/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]
[/TR]
[TR]
[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="colspan: 8"] If temp = "L" Then spleave = spleave + 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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="colspan: 8"] If temp = "A" Then leave = leave + 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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="colspan: 7"] If temp = "S" Then sick = sick + 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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="colspan: 9"] If temp = "W" Then worked = worked + 12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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="colspan: 14"] If Lenn > 1 And Left(Cells(k, j), 1) = "W" Then GoTo 50 Else GoTo 51[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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="colspan: 14"]50 If suff > 12 Then worked = worked + 12: ot = ot + suff - 12: GoTo 60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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="colspan: 7"] worked = worked + suff: GoTo 60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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="colspan: 17"]51 If Lenn > 1 And Left(Cells(k, j), 1) = "L" Then LOOO = LOOO + Mid(Cells(k, j), 2, 2)[/TD]
[TD][/TD]
[/TR]
[TR]
[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="colspan: 3"]60 Next k[/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]
[/TR]
[TR]
[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="colspan: 2"] Next j[/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]
[/TR]
[TR]
[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="colspan: 5"] Cells(32, 8) = worked[/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]
[/TR]
[TR]
[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="colspan: 4"] Cells(33, 8) = ot[/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]
[/TR]
[TR]
[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="colspan: 4"] Cells(34, 8) = sick[/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]
[/TR]
[TR]
[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="colspan: 5"] Cells(35, 8) = leave[/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]
[/TR]
[TR]
[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="colspan: 5"] Cells(37, 8) = spleave[/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]
[/TR]
[TR]
[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="colspan: 5"] Cells(36, 8) = LOOO[/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]
[/TR]
[TR]
[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="colspan: 3"]100 End Sub[/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]
[/TR]
[TR]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]