Count number of shifts...

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!

I assume your export is to a text file. If so write a macro to import the
data. Put the shift into column A then used B - G to store a zero if the day
is a rest day and a one if a work day. This can be done by checking each of
the first seven characters in each line.

This should make the data more managable
--
Tony Green


"[email protected]" 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!
>
>

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

"[email protected]" 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!
>
>

I usually export to Access - then move to Excel.

Tony, you're absolutely right. But replacing a "M" with a 1, doesn't
reduce the number of day-of -week variations. There would still be a
huge number of combinations.

Tom, maybe I'm missing something, but all of my data is approx. 700+
rows and each is different. The schedule and shift data are not in
separate rows. Is there a way I can quickly format rows that contain
MTW--YS, FT3 to the format you define?

Thanks guys!

I never assumed they were on separate rows since that isn't what you
displayed.

I do have some corrections. In all cases, the formula should be like:

C2: =IF(SUM(COUNTIF(A2,{"*Y*","*S*"}))=2,1,0)
D2: =IF(SUM(COUNTIF(A2,{"*Y*","*S*"}))=1,1,0)
E2: =IF(SUM(COUNTIF(A2,{"*Y*","*S*"}))=0,1,0)

Also, you don't need anything in the Column area and the 3 buttons in the
data area shoud be SUM of instead of COUNT of.

I have implemented this with your data and it produced:



Data
Cat Sum of Double Sum of Single Sum of Mon-Fri
FT1 2 0 0
FT3 0 0 2
PT2 1 0 0
Grand Total 3 0 2


putting in 3 formulas and then filling down the column takes about 15
seconds.

Not sure what you consider fast. Buiding the pivot table takes about 30
seconds once you have it worked out.

I will send you sample workbook at [email protected] assuming it is legit.
--
Regards,
Tom Ogilvy


"[email protected]" wrote:

> I usually export to Access - then move to Excel.
>
> Tony, you're absolutely right. But replacing a "M" with a 1, doesn't
> reduce the number of day-of -week variations. There would still be a
> huge number of combinations.
>
> Tom, maybe I'm missing something, but all of my data is approx. 700+
> rows and each is different. The schedule and shift data are not in
> separate rows. Is there a way I can quickly format rows that contain
> MTW--YS, FT3 to the format you define?
>
> Thanks guys!
>
>

Tom, just got into the office. The sample workbook helped a great
deal! I exported the data, keyed your formula, and set up the pivot
table. Works like a charm!

I must have misread your first reply, thinking that shift and category
had to be in every other row. My apologies.

You've helped me a great deal - thank you!!!

Best regards,
Jason