| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Instances of sick (apologies to those that have answered it before
I have a data base that records the amount of holidays and sickness taken
each month and then colates all the info into a summery sheet What I'm after is a macro or formula that totals up the amount of sick instances each month and gives me a running total for the year I will try to explain how it is set up Each month has a differerant tab In column A from A4 to A100 are a list of names In Row 3 Column D to AH are a list of all the dates 1st to 31st In each of the rows i record either a H for Holiday or an S for sickness The problem i have is that everyone is on differant shift patterns (4 on 4 off or days mon to fri) so will need it tp reconises that if the gap is more than 5 days part from the last instance then it is a new instance if it is less than 5 then it's the same instance For example I would like the answer for the following to return 3 Sick instances D4 = H E4 = Blank F4 = Blank G4 = Blank H4 = Blank I4 = S J4 = S K4 = Blank L4 = Blank M4 = Blank N4 = Blank O4 = Blank P4 = Blank S4 = S T4 = S U4 = Blank V4 = Blank W4 = Blank X4 = Blank Y4 = Blank Z4 = Blank AA4 = Blank AB4 = S AC4 = S AD4 = blank AE4 = Blank AF4 = Blank AG4 = Blank AH4 = Blank I also need it to somehow look at the end of each month to capture whether it is still the same or new instances I can create a new tab to include the answers, set up the same way with the same names |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Instances of sick (apologies to those that have answered it before
If there is an "instance" which spans two months (end of month1, start of
month2) which month is it recorded against? In the pattern below, how many instances are there given there are 3 sets of "S"s but each is within 5 days of the previous? s blank blank blank blank s s s s blank blank blank blank s s "louiscourtney" wrote: I have a data base that records the amount of holidays and sickness taken each month and then colates all the info into a summery sheet What I'm after is a macro or formula that totals up the amount of sick instances each month and gives me a running total for the year I will try to explain how it is set up Each month has a differerant tab In column A from A4 to A100 are a list of names In Row 3 Column D to AH are a list of all the dates 1st to 31st In each of the rows i record either a H for Holiday or an S for sickness The problem i have is that everyone is on differant shift patterns (4 on 4 off or days mon to fri) so will need it tp reconises that if the gap is more than 5 days part from the last instance then it is a new instance if it is less than 5 then it's the same instance For example I would like the answer for the following to return 3 Sick instances D4 = H E4 = Blank F4 = Blank G4 = Blank H4 = Blank I4 = S J4 = S K4 = Blank L4 = Blank M4 = Blank N4 = Blank O4 = Blank P4 = Blank S4 = S T4 = S U4 = Blank V4 = Blank W4 = Blank X4 = Blank Y4 = Blank Z4 = Blank AA4 = Blank AB4 = S AC4 = S AD4 = blank AE4 = Blank AF4 = Blank AG4 = Blank AH4 = Blank I also need it to somehow look at the end of each month to capture whether it is still the same or new instances I can create a new tab to include the answers, set up the same way with the same names |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Instances of sick (apologies to those that have answered it be
Further questions: working on the NAAFOF principle (Never Assume Anything,
Find Out First): Are shifts always in the same week i.e cannot overlap two weeks (e.g. Thursday, Friday, Monday, Tuesday)? If so, is an "instance" therefore any sickness which occurs in a given week? "Toppers" wrote: If there is an "instance" which spans two months (end of month1, start of month2) which month is it recorded against? In the pattern below, how many instances are there given there are 3 sets of "S"s but each is within 5 days of the previous? s blank blank blank blank s s s s blank blank blank blank s s "louiscourtney" wrote: I have a data base that records the amount of holidays and sickness taken each month and then colates all the info into a summery sheet What I'm after is a macro or formula that totals up the amount of sick instances each month and gives me a running total for the year I will try to explain how it is set up Each month has a differerant tab In column A from A4 to A100 are a list of names In Row 3 Column D to AH are a list of all the dates 1st to 31st In each of the rows i record either a H for Holiday or an S for sickness The problem i have is that everyone is on differant shift patterns (4 on 4 off or days mon to fri) so will need it tp reconises that if the gap is more than 5 days part from the last instance then it is a new instance if it is less than 5 then it's the same instance For example I would like the answer for the following to return 3 Sick instances D4 = H E4 = Blank F4 = Blank G4 = Blank H4 = Blank I4 = S J4 = S K4 = Blank L4 = Blank M4 = Blank N4 = Blank O4 = Blank P4 = Blank S4 = S T4 = S U4 = Blank V4 = Blank W4 = Blank X4 = Blank Y4 = Blank Z4 = Blank AA4 = Blank AB4 = S AC4 = S AD4 = blank AE4 = Blank AF4 = Blank AG4 = Blank AH4 = Blank I also need it to somehow look at the end of each month to capture whether it is still the same or new instances I can create a new tab to include the answers, set up the same way with the same names |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Instances of sick (apologies to those that have answered it be
Topper
Shifts can overlapp weeks Friday saturday sunday monday etc Looking at instances over the month and maybe a running total for the year Regards Shane "Toppers" wrote: Further questions: working on the NAAFOF principle (Never Assume Anything, Find Out First): Are shifts always in the same week i.e cannot overlap two weeks (e.g. Thursday, Friday, Monday, Tuesday)? If so, is an "instance" therefore any sickness which occurs in a given week? "Toppers" wrote: If there is an "instance" which spans two months (end of month1, start of month2) which month is it recorded against? In the pattern below, how many instances are there given there are 3 sets of "S"s but each is within 5 days of the previous? s blank blank blank blank s s s s blank blank blank blank s s "louiscourtney" wrote: I have a data base that records the amount of holidays and sickness taken each month and then colates all the info into a summery sheet What I'm after is a macro or formula that totals up the amount of sick instances each month and gives me a running total for the year I will try to explain how it is set up Each month has a differerant tab In column A from A4 to A100 are a list of names In Row 3 Column D to AH are a list of all the dates 1st to 31st In each of the rows i record either a H for Holiday or an S for sickness The problem i have is that everyone is on differant shift patterns (4 on 4 off or days mon to fri) so will need it tp reconises that if the gap is more than 5 days part from the last instance then it is a new instance if it is less than 5 then it's the same instance For example I would like the answer for the following to return 3 Sick instances D4 = H E4 = Blank F4 = Blank G4 = Blank H4 = Blank I4 = S J4 = S K4 = Blank L4 = Blank M4 = Blank N4 = Blank O4 = Blank P4 = Blank S4 = S T4 = S U4 = Blank V4 = Blank W4 = Blank X4 = Blank Y4 = Blank Z4 = Blank AA4 = Blank AB4 = S AC4 = S AD4 = blank AE4 = Blank AF4 = Blank AG4 = Blank AH4 = Blank I also need it to somehow look at the end of each month to capture whether it is still the same or new instances I can create a new tab to include the answers, set up the same way with the same names |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Instances of sick (apologies to those that have answered it be
.... and answer to my first posting re number of instances in the following ..
In the pattern below, how many instances are there given there are 3 sets of "S"s but each is within 5 days of the previous? s blank blank blank blank s s s s blank blank blank blank s s "louiscourtney" wrote: "louiscourtney" wrote: Topper Shifts can overlapp weeks Friday saturday sunday monday etc Looking at instances over the month and maybe a running total for the year Regards Shane "Toppers" wrote: Further questions: working on the NAAFOF principle (Never Assume Anything, Find Out First): Are shifts always in the same week i.e cannot overlap two weeks (e.g. Thursday, Friday, Monday, Tuesday)? If so, is an "instance" therefore any sickness which occurs in a given week? "Toppers" wrote: If there is an "instance" which spans two months (end of month1, start of month2) which month is it recorded against? In the pattern below, how many instances are there given there are 3 sets of "S"s but each is within 5 days of the previous? s blank blank blank blank s s s s blank blank blank blank s s "louiscourtney" wrote: I have a data base that records the amount of holidays and sickness taken each month and then colates all the info into a summery sheet What I'm after is a macro or formula that totals up the amount of sick instances each month and gives me a running total for the year I will try to explain how it is set up Each month has a differerant tab In column A from A4 to A100 are a list of names In Row 3 Column D to AH are a list of all the dates 1st to 31st In each of the rows i record either a H for Holiday or an S for sickness The problem i have is that everyone is on differant shift patterns (4 on 4 off or days mon to fri) so will need it tp reconises that if the gap is more than 5 days part from the last instance then it is a new instance if it is less than 5 then it's the same instance For example I would like the answer for the following to return 3 Sick instances D4 = H E4 = Blank F4 = Blank G4 = Blank H4 = Blank I4 = S J4 = S K4 = Blank L4 = Blank M4 = Blank N4 = Blank O4 = Blank P4 = Blank S4 = S T4 = S U4 = Blank V4 = Blank W4 = Blank X4 = Blank Y4 = Blank Z4 = Blank AA4 = Blank AB4 = S AC4 = S AD4 = blank AE4 = Blank AF4 = Blank AG4 = Blank AH4 = Blank I also need it to somehow look at the end of each month to capture whether it is still the same or new instances I can create a new tab to include the answers, set up the same way with the same names |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Instances of sick (apologies to those that have answered it be
There are 3 instances and a total of 6 days (maybe if i showed differantly)
as below Jan 01 = H Jan 02 = Off shift Jan 03 = Off Shift Jan 04 = Off Shift Jan 05 = Off shift Jan 06 = S Jan 07 = S Jan 08 = Worked Jan 09 = Worked Jan 10 = Off shift Jan 11 = Off shift Jan 12 = Off shift Jan 13 = Off shift Jan 14 = S Jan 15 = worked Jan 16 = Worked Jan 17 = Worked Jan 18 = off shift Jan 19 = off shift Jan 20 = off shift Jan 21 = off shift Jan 22 = worked Jan 23 = worked Jan 24 = S Jan 25 = S Jan 26 = off shift Jan 27 = off shift Jan 28 = off shift Jan 29 = off shift Jan 30 = S Jan 31 = Worked "Toppers" wrote: ... and answer to my first posting re number of instances in the following .. In the pattern below, how many instances are there given there are 3 sets of "S"s but each is within 5 days of the previous? s blank blank blank blank s s s s blank blank blank blank s s "louiscourtney" wrote: "louiscourtney" wrote: Topper Shifts can overlapp weeks Friday saturday sunday monday etc Looking at instances over the month and maybe a running total for the year Regards Shane "Toppers" wrote: Further questions: working on the NAAFOF principle (Never Assume Anything, Find Out First): Are shifts always in the same week i.e cannot overlap two weeks (e.g. Thursday, Friday, Monday, Tuesday)? If so, is an "instance" therefore any sickness which occurs in a given week? "Toppers" wrote: If there is an "instance" which spans two months (end of month1, start of month2) which month is it recorded against? In the pattern below, how many instances are there given there are 3 sets of "S"s but each is within 5 days of the previous? s blank blank blank blank s s s s blank blank blank blank s s "louiscourtney" wrote: I have a data base that records the amount of holidays and sickness taken each month and then colates all the info into a summery sheet What I'm after is a macro or formula that totals up the amount of sick instances each month and gives me a running total for the year I will try to explain how it is set up Each month has a differerant tab In column A from A4 to A100 are a list of names In Row 3 Column D to AH are a list of all the dates 1st to 31st In each of the rows i record either a H for Holiday or an S for sickness The problem i have is that everyone is on differant shift patterns (4 on 4 off or days mon to fri) so will need it tp reconises that if the gap is more than 5 days part from the last instance then it is a new instance if it is less than 5 then it's the same instance For example I would like the answer for the following to return 3 Sick instances D4 = H E4 = Blank F4 = Blank G4 = Blank H4 = Blank I4 = S J4 = S K4 = Blank L4 = Blank M4 = Blank N4 = Blank O4 = Blank P4 = Blank S4 = S T4 = S U4 = Blank V4 = Blank W4 = Blank X4 = Blank Y4 = Blank Z4 = Blank AA4 = Blank AB4 = S AC4 = S AD4 = blank AE4 = Blank AF4 = Blank AG4 = Blank AH4 = Blank I also need it to somehow look at the end of each month to capture whether it is still the same or new instances I can create a new tab to include the answers, set up the same way with the same names |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Instances of sick (apologies to those that have answered it be
Maybe if it is shown like this you can understand it clearer?
I need the answer to show 3 instances / 6 days, then the promblem is when it goes into the next month and i have no idea as to this Thanks Jan 01 = H Jan 02 = Off shift Jan 03 = Off Shift Jan 04 = Off Shift Jan 05 = Off shift Jan 06 = S Jan 07 = S Jan 08 = Worked Jan 09 = Worked Jan 10 = Off shift Jan 11 = Off shift Jan 12 = Off shift Jan 13 = Off shift Jan 14 = S Jan 15 = worked Jan 16 = Worked Jan 17 = Worked Jan 18 = off shift Jan 19 = off shift Jan 20 = off shift Jan 21 = off shift Jan 22 = worked Jan 23 = worked Jan 24 = S Jan 25 = S Jan 26 = off shift Jan 27 = off shift Jan 28 = off shift Jan 29 = off shift Jan 30 = S Jan 31 = Worked "Toppers" wrote: ... and answer to my first posting re number of instances in the following .. In the pattern below, how many instances are there given there are 3 sets of "S"s but each is within 5 days of the previous? s blank blank blank blank s s s s blank blank blank blank s s "louiscourtney" wrote: "louiscourtney" wrote: Topper Shifts can overlapp weeks Friday saturday sunday monday etc Looking at instances over the month and maybe a running total for the year Regards Shane "Toppers" wrote: Further questions: working on the NAAFOF principle (Never Assume Anything, Find Out First): Are shifts always in the same week i.e cannot overlap two weeks (e.g. Thursday, Friday, Monday, Tuesday)? If so, is an "instance" therefore any sickness which occurs in a given week? "Toppers" wrote: If there is an "instance" which spans two months (end of month1, start of month2) which month is it recorded against? In the pattern below, how many instances are there given there are 3 sets of "S"s but each is within 5 days of the previous? s blank blank blank blank s s s s blank blank blank blank s s "louiscourtney" wrote: I have a data base that records the amount of holidays and sickness taken each month and then colates all the info into a summery sheet What I'm after is a macro or formula that totals up the amount of sick instances each month and gives me a running total for the year I will try to explain how it is set up Each month has a differerant tab In column A from A4 to A100 are a list of names In Row 3 Column D to AH are a list of all the dates 1st to 31st In each of the rows i record either a H for Holiday or an S for sickness The problem i have is that everyone is on differant shift patterns (4 on 4 off or days mon to fri) so will need it tp reconises that if the gap is more than 5 days part from the last instance then it is a new instance if it is less than 5 then it's the same instance For example I would like the answer for the following to return 3 Sick instances D4 = H E4 = Blank F4 = Blank G4 = Blank H4 = Blank I4 = S J4 = S K4 = Blank L4 = Blank M4 = Blank N4 = Blank O4 = Blank P4 = Blank S4 = S T4 = S U4 = Blank V4 = Blank W4 = Blank X4 = Blank Y4 = Blank Z4 = Blank AA4 = Blank AB4 = S AC4 = S AD4 = blank AE4 = Blank AF4 = Blank AG4 = Blank AH4 = Blank I also need it to somehow look at the end of each month to capture whether it is still the same or new instances I can create a new tab to include the answers, set up the same way with the same names |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Instances of sick (apologies to those that have answered it be
.. and this .... (you are ducking the difficult !) ....
Is 14th to 31st ONE instance as there are NO "gaps" 5 days (17th - 22nd =5 ,25th - 30th = 5) Sorry to be so pedantic ..... Jan 01 = H Jan 02 = Off shift Jan 03 = Off Shift Jan 04 = Off Shift Jan 05 = Off shift Jan 06 = S Jan 07 = S Jan 08 = Worked Jan 09 = Worked Jan 10 = Off shift Jan 11 = Off shift Jan 12 = Off shift Jan 13 = Off shift Jan 14 = S Jan 15 = worked Jan 16 = Worked Jan 17 = S Jan 18 = off shift Jan 19 = off shift Jan 20 = off shift Jan 21 = off shift Jan 22 = S Jan 23 = worked Jan 24 = S Jan 25 = S Jan 26 = off shift Jan 27 = off shift Jan 28 = off shift Jan 29 = off shift Jan 30 = S Jan 31 = Worked "louiscourtney" wrote: Maybe if it is shown like this you can understand it clearer? I need the answer to show 3 instances / 6 days, then the promblem is when it goes into the next month and i have no idea as to this Thanks Jan 01 = H Jan 02 = Off shift Jan 03 = Off Shift Jan 04 = Off Shift Jan 05 = Off shift Jan 06 = S Jan 07 = S Jan 08 = Worked Jan 09 = Worked Jan 10 = Off shift Jan 11 = Off shift Jan 12 = Off shift Jan 13 = Off shift Jan 14 = S Jan 15 = worked Jan 16 = Worked Jan 17 = Worked Jan 18 = off shift Jan 19 = off shift Jan 20 = off shift Jan 21 = off shift Jan 22 = worked Jan 23 = worked Jan 24 = S Jan 25 = S Jan 26 = off shift Jan 27 = off shift Jan 28 = off shift Jan 29 = off shift Jan 30 = S Jan 31 = Worked "Toppers" wrote: ... and answer to my first posting re number of instances in the following .. In the pattern below, how many instances are there given there are 3 sets of "S"s but each is within 5 days of the previous? s blank blank blank blank s s s s blank blank blank blank s s "louiscourtney" wrote: "louiscourtney" wrote: Topper Shifts can overlapp weeks Friday saturday sunday monday etc Looking at instances over the month and maybe a running total for the year Regards Shane "Toppers" wrote: Further questions: working on the NAAFOF principle (Never Assume Anything, Find Out First): Are shifts always in the same week i.e cannot overlap two weeks (e.g. Thursday, Friday, Monday, Tuesday)? If so, is an "instance" therefore any sickness which occurs in a given week? "Toppers" wrote: If there is an "instance" which spans two months (end of month1, start of month2) which month is it recorded against? In the pattern below, how many instances are there given there are 3 sets of "S"s but each is within 5 days of the previous? s blank blank blank blank s s s s blank blank blank blank s s "louiscourtney" wrote: I have a data base that records the amount of holidays and sickness taken each month and then colates all the info into a summery sheet What I'm after is a macro or formula that totals up the amount of sick instances each month and gives me a running total for the year I will try to explain how it is set up Each month has a differerant tab In column A from A4 to A100 are a list of names In Row 3 Column D to AH are a list of all the dates 1st to 31st In each of the rows i record either a H for Holiday or an S for sickness The problem i have is that everyone is on differant shift patterns (4 on 4 off or days mon to fri) so will need it tp reconises that if the gap is more than 5 days part from the last instance then it is a new instance if it is less than 5 then it's the same instance For example I would like the answer for the following to return 3 Sick instances D4 = H E4 = Blank F4 = Blank G4 = Blank H4 = Blank I4 = S J4 = S K4 = Blank L4 = Blank M4 = Blank N4 = Blank O4 = Blank P4 = Blank S4 = S T4 = S U4 = Blank V4 = Blank W4 = Blank X4 = Blank Y4 = Blank Z4 = Blank AA4 = Blank AB4 = S AC4 = S AD4 = blank AE4 = Blank AF4 = Blank AG4 = Blank AH4 = Blank I also need it to somehow look at the end of each month to capture whether it is still the same or new instances I can create a new tab to include the answers, set up the same way with the same names |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Instances of sick (apologies to those that have answered it be
No it will be two instances as they came into work on the 15th
Sorry to be so difficult !! "Toppers" wrote: . and this .... (you are ducking the difficult !) .... Is 14th to 31st ONE instance as there are NO "gaps" 5 days (17th - 22nd =5 ,25th - 30th = 5) Sorry to be so pedantic ..... Jan 01 = H Jan 02 = Off shift Jan 03 = Off Shift Jan 04 = Off Shift Jan 05 = Off shift Jan 06 = S Jan 07 = S Jan 08 = Worked Jan 09 = Worked Jan 10 = Off shift Jan 11 = Off shift Jan 12 = Off shift Jan 13 = Off shift Jan 14 = S Jan 15 = worked Jan 16 = Worked Jan 17 = S Jan 18 = off shift Jan 19 = off shift Jan 20 = off shift Jan 21 = off shift Jan 22 = S Jan 23 = worked Jan 24 = S Jan 25 = S Jan 26 = off shift Jan 27 = off shift Jan 28 = off shift Jan 29 = off shift Jan 30 = S Jan 31 = Worked "louiscourtney" wrote: Maybe if it is shown like this you can understand it clearer? I need the answer to show 3 instances / 6 days, then the promblem is when it goes into the next month and i have no idea as to this Thanks Jan 01 = H Jan 02 = Off shift Jan 03 = Off Shift Jan 04 = Off Shift Jan 05 = Off shift Jan 06 = S Jan 07 = S Jan 08 = Worked Jan 09 = Worked Jan 10 = Off shift Jan 11 = Off shift Jan 12 = Off shift Jan 13 = Off shift Jan 14 = S Jan 15 = worked Jan 16 = Worked Jan 17 = Worked Jan 18 = off shift Jan 19 = off shift Jan 20 = off shift Jan 21 = off shift Jan 22 = worked Jan 23 = worked Jan 24 = S Jan 25 = S Jan 26 = off shift Jan 27 = off shift Jan 28 = off shift Jan 29 = off shift Jan 30 = S Jan 31 = Worked "Toppers" wrote: ... and answer to my first posting re number of instances in the following .. In the pattern below, how many instances are there given there are 3 sets of "S"s but each is within 5 days of the previous? s blank blank blank blank s s s s blank blank blank blank s s "louiscourtney" wrote: "louiscourtney" wrote: Topper Shifts can overlapp weeks Friday saturday sunday monday etc Looking at instances over the month and maybe a running total for the year Regards Shane "Toppers" wrote: Further questions: working on the NAAFOF principle (Never Assume Anything, Find Out First): Are shifts always in the same week i.e cannot overlap two weeks (e.g. Thursday, Friday, Monday, Tuesday)? If so, is an "instance" therefore any sickness which occurs in a given week? "Toppers" wrote: If there is an "instance" which spans two months (end of month1, start of month2) which month is it recorded against? In the pattern below, how many instances are there given there are 3 sets of "S"s but each is within 5 days of the previous? s blank blank blank blank s s s s blank blank blank blank s s "louiscourtney" wrote: I have a data base that records the amount of holidays and sickness taken each month and then colates all the info into a summery sheet What I'm after is a macro or formula that totals up the amount of sick instances each month and gives me a running total for the year I will try to explain how it is set up Each month has a differerant tab In column A from A4 to A100 are a list of names In Row 3 Column D to AH are a list of all the dates 1st to 31st In each of the rows i record either a H for Holiday or an S for sickness The problem i have is that everyone is on differant shift patterns (4 on 4 off or days mon to fri) so will need it tp reconises that if the gap is more than 5 days part from the last instance then it is a new instance if it is less than 5 then it's the same instance For example I would like the answer for the following to return 3 Sick instances D4 = H E4 = Blank F4 = Blank G4 = Blank H4 = Blank I4 = S J4 = S K4 = Blank L4 = Blank M4 = Blank N4 = Blank O4 = Blank P4 = Blank S4 = S T4 = S U4 = Blank V4 = Blank W4 = Blank X4 = Blank Y4 = Blank Z4 = Blank AA4 = Blank AB4 = S AC4 = S AD4 = blank AE4 = Blank AF4 = Blank AG4 = Blank AH4 = Blank I also need it to somehow look at the end of each month to capture whether it is still the same or new instances I can create a new tab to include the answers, set up the same way with the same names |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Instances of sick (apologies to those that have answered it be
Please e-mail me and I will send a sample w/book to check if monthly
calculation is correct. toppers <at NOSPAMjohntopley.fsnet.co.uk "louiscourtney" wrote: No it will be two instances as they came into work on the 15th Sorry to be so difficult !! "Toppers" wrote: . and this .... (you are ducking the difficult !) .... Is 14th to 31st ONE instance as there are NO "gaps" 5 days (17th - 22nd =5 ,25th - 30th = 5) Sorry to be so pedantic ..... Jan 01 = H Jan 02 = Off shift Jan 03 = Off Shift Jan 04 = Off Shift Jan 05 = Off shift Jan 06 = S Jan 07 = S Jan 08 = Worked Jan 09 = Worked Jan 10 = Off shift Jan 11 = Off shift Jan 12 = Off shift Jan 13 = Off shift Jan 14 = S Jan 15 = worked Jan 16 = Worked Jan 17 = S Jan 18 = off shift Jan 19 = off shift Jan 20 = off shift Jan 21 = off shift Jan 22 = S Jan 23 = worked Jan 24 = S Jan 25 = S Jan 26 = off shift Jan 27 = off shift Jan 28 = off shift Jan 29 = off shift Jan 30 = S Jan 31 = Worked "louiscourtney" wrote: Maybe if it is shown like this you can understand it clearer? I need the answer to show 3 instances / 6 days, then the promblem is when it goes into the next month and i have no idea as to this Thanks Jan 01 = H Jan 02 = Off shift Jan 03 = Off Shift Jan 04 = Off Shift Jan 05 = Off shift Jan 06 = S Jan 07 = S Jan 08 = Worked Jan 09 = Worked Jan 10 = Off shift Jan 11 = Off shift Jan 12 = Off shift Jan 13 = Off shift Jan 14 = S Jan 15 = worked Jan 16 = Worked Jan 17 = Worked Jan 18 = off shift Jan 19 = off shift Jan 20 = off shift Jan 21 = off shift Jan 22 = worked Jan 23 = worked Jan 24 = S Jan 25 = S Jan 26 = off shift Jan 27 = off shift Jan 28 = off shift Jan 29 = off shift Jan 30 = S Jan 31 = Worked "Toppers" wrote: ... and answer to my first posting re number of instances in the following .. In the pattern below, how many instances are there given there are 3 sets of "S"s but each is within 5 days of the previous? s blank blank blank blank s s s s blank blank blank blank s s "louiscourtney" wrote: "louiscourtney" wrote: Topper Shifts can overlapp weeks Friday saturday sunday monday etc Looking at instances over the month and maybe a running total for the year Regards Shane "Toppers" wrote: Further questions: working on the NAAFOF principle (Never Assume Anything, Find Out First): Are shifts always in the same week i.e cannot overlap two weeks (e.g. Thursday, Friday, Monday, Tuesday)? If so, is an "instance" therefore any sickness which occurs in a given week? "Toppers" wrote: If there is an "instance" which spans two months (end of month1, start of month2) which month is it recorded against? In the pattern below, how many instances are there given there are 3 sets of "S"s but each is within 5 days of the previous? s blank blank blank blank s s s s blank blank blank blank s s "louiscourtney" wrote: I have a data base that records the amount of holidays and sickness taken each month and then colates all the info into a summery sheet What I'm after is a macro or formula that totals up the amount of sick instances each month and gives me a running total for the year I will try to explain how it is set up Each month has a differerant tab In column A from A4 to A100 are a list of names In Row 3 Column D to AH are a list of all the dates 1st to 31st In each of the rows i record either a H for Holiday or an S for sickness The problem i have is that everyone is on differant shift patterns (4 on 4 off or days mon to fri) so will need it tp reconises that if the gap is more than 5 days part from the last instance then it is a new instance if it is less than 5 then it's the same instance For example I would like the answer for the following to return 3 Sick instances D4 = H E4 = Blank F4 = Blank G4 = Blank H4 = Blank I4 = S J4 = S K4 = Blank L4 = Blank M4 = Blank N4 = Blank O4 = Blank P4 = Blank S4 = S T4 = S U4 = Blank V4 = Blank W4 = Blank X4 = Blank Y4 = Blank Z4 = Blank AA4 = Blank AB4 = S AC4 = S AD4 = blank AE4 = Blank AF4 = Blank AG4 = Blank AH4 = Blank I also need it to somehow look at the end of each month to capture whether it is still the same or new instances I can create a new tab to include the answers, set up the same way with the same names |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Instances of sick (apologies to those that have answered it be
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I can't see my last post, but its been answered- help! | Excel Worksheet Functions | |||
| Thanks to all who answered my questions | Excel Discussion (Misc queries) | |||
| Apologies for triple post | New Users to Excel | |||
| This has been asked, but not answered... | Excel Discussion (Misc queries) | |||
| Comparing Named ranges apologies for the dodgey post below | Excel Worksheet Functions | |||
Linear Mode
