Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count number of shifts...
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 " 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count number of shifts...
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count number of shifts...
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 assuming it is legit. -- Regards, Tom Ogilvy " 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count number of shifts...
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count rows and insert number to count them. | Excel Discussion (Misc queries) | |||
count each cell that have a number and take that number and count. | Excel Discussion (Misc queries) | |||
Number count - postcodes and need to count the numbers of tim... | Excel Discussion (Misc queries) | |||
Count number of times a specific number is displayed in a cell ran | Excel Worksheet Functions | |||
Count number of times a specific number is displayed in cells | Excel Programming |