Count number of shifts...
A1: Shift
A2: M--RFYS
B1: Cat
B2: FT1
C1: Double
C2: =if(sum(countif(A2,{"Y","S"}))=2,1,0)
D1: Single
D2: =if(sum(countif(A2,{"Y","S"}))=1,1,0)
E1: Mon-Fri
E2: =if(sum(countif(A2,{"Y","S"}))=0,1,0)
Now select C2:E2 and drag fill down the column
then select A1:E (lastrow) and do
Data=Pivot Table Report
Select layout and Drag the buttons:
Shift as a row field
Double to the column area
Single to the column area
Mon-Fri to the column area
Double to the data area (make sure it says COUNT of)
Single to the data area (make sure it says COUNT of)
Mon-Fri to the data area (make sure it says COUNT of)
Click OK
Click finish.
--
Regards,
Tom Ogilvy
Mon-Fri to the column area
" wrote:
We currently have approx. 700 schedules in our scheduling software and
we often need to export them to count how many Mon - Fri, Tue - Sat,
Sun - Thr shifts. Being a 24x7 operation, the combination of days
worked vary alot. Ex. Mon, Tue, Fri, Sat, Sun. The format of our
export is MTWRF--. Dashes always indicate days off. In addition, to
count the number of 1st, 2nd, and 3rd shift schedules - an indicator
accompanies each schedule. These are FT1, FT2, FT3, PT1, PT2, PT3. FT
= Full Time (number = shift).
I have tried IF statements, countif, etc... My problem is that there
are so many variations of days off that I cannot place the logic in
them. To further their complexity, some are Part Time and some work 5
x 8 schedules, others have 4 x 10's.
Can anyone assist me some code or formula that will read:
M--RFYS FT1
---RFYS FT1
MTWRF-- FT3
-TWRF-- FT3
-T---YS PT2
and tell me that I have 2 Monday - Friday FT3 schedules / 2 double-day
weekend FT1 schedules / 1 double-day PT2 schedule.
We only need their Shift (FT1, PT1, etc.), single or double day
weekend, or no weekend days (Mon - Fri) in the reports.
Any and all help is appreciated!
|