Shift Pattern

[daleholden]

Is it possible to have a 10 week shift pattern listed in excel. The to have a macro so when you put up a set of dates you insert on the correct date then run the macro and it enters all the shifts to the correct dates??

 

[daleholden]

Ok i would like to enter a cell run a macro and the shift pattern be filled in automatically with the correct shift on the correct date



_________________
People prefer to follow those who help them, not those who intimidate them.
This message was edited by daleholden on 2002-09-23 19:39

 

[PaddyD]

222.xls
	A	B	C	D	E	F	G	H
1		MON	TUES	WED	THURS	FRI	SAT	SUN
2		05/03/2001	06/03/2001	07/03/2001	08/03/2001	09/03/2001	10/03/2001	11/03/2001
3	1	3pm x 12pm	10pm x 7am	10pm x 7am	RD	RD	RD	RD
4		12/03/2001	13/03/2001	14/03/2001	15/03/2001	16/03/2001	17/03/2001	18/03/2001
5	2	7am x4pm	7am x 4pm	3pm x 12pm	3pm x 12pm	10pm x 7am	10pm x 7am	RD
6		19/03/2001	20/03/2001	21/03/2001	22/03/2001	23/03/2001	24/03/2001	25/03/2001
7	3	RD	RD	SPARE	7am x 4pm	7am x 5pm	5pm x 3am	4pm x 11pm
8		26/03/2001	27/03/2001	28/03/2001	29/03/2001	30/03/2001	31/03/2001	01/04/2001
9	4	10pm x 7am	10pm x 7am	RD	RD	RD	SPARE	7am x 4pm
10		02/04/2001	03/04/2001	04/04/2001	05/04/2001	06/04/2001	07/04/2001	08/04/2001
11	5	7am x 4pm	7am x 4pm	3pm x 12pm	10pm x 7am	10pm x 7am	RD	RD
12		09/04/2001	10/04/2001	11/04/2001	12/04/2001	13/04/2001	14/04/2001	15/04/2001
13	6	RD	RD	7am x 4pm	7am x 4pm	RD	RD	RD
14		16/04/2001	17/04/2001	18/04/2001	19/04/2001	20/04/2001	21/04/2001	22/04/2001
15	7	8am x 4pm	8am x 4pm	8am x 4pm	8am x 4pm	8am x 4pm	7am x 5pm	7am x 4pm
16		23/04/2001	24/04/2001	25/04/2001	26/04/2001	27/04/2001	28/04/2001	29/04/2001
17	8	8am x 4pm	8am x 4pm	8am x 4pm	8am x 4pm	8am x 4pm	RD	RD
18		30/04/2001	01/05/2001	02/05/2001	03/05/2001	04/05/2001	05/05/2001	06/05/2001
19	9	RD	7am x 4pm	7am x 4pm	3pm x 12pm	5pm x 3am	10pm x 7am	10pm x 7am
20		07/05/2001	08/05/2001	09/05/2001	10/05/2001	11/05/2001	12/05/2001	13/05/2001
21	10	RD	RD	RD	RD	7am x 5pm	7am x 5pm	4pm x 11pm
2000

 

[PaddyD]

Dale,

I think you must have selected to disable html on the post!

Paddy

 

[PaddyD]

Dale,

My remark about mind-reading stands! Could you please explain in 'natural language' what you want. How do we know what shift patterns are to go in each day? I assume RD = Rest Day = weekends?

Please be more explicit about your requirements...

Paddy

 

[daleholden]

Sorry Paddy

Well here is the complete 10 week pattern, the shift pattern repeats it self exactly every 10 weeks.

But in stead of cutting and pasting to different days. I would like only have to enter the full pattern then when i selected an empty 10 week pattern i would enter cell on Monday week 1 run a macro and it fills in the the whole 10 weeks.

Does that make sense?

 

[PaddyD]

If the pattern always starts on a Monday & runs for 10 weeks, why not keep the shift times & have the dates change with a formula based on the data in the 'first monday of the 10 week period' cell?

Paddy

 

[daleholden]

Sure how would i do that?

Would the formula change everydate?

So would i select the 1st date then it changed all the others?

 

[PaddyD]

Ony B2 needs to have a date entered:

Book7
	A	B	C	D	E	F	G	H
1		MON	TUE	WED	THUR	FRI	SAT	SUN
2	1	30/09/2002	1/10/2002	2/10/2002	3/10/2002	4/10/2002	5/10/2002	6/10/2002
3
4	2	7/10/2002	8/10/2002	9/10/2002	10/10/2002	11/10/2002	12/10/2002	13/10/2002
5
6	3	14/10/2002	15/10/2002	16/10/2002	17/10/2002	18/10/2002	19/10/2002	20/10/2002
7
8	4	21/10/2002	22/10/2002	23/10/2002	24/10/2002	25/10/2002	26/10/2002	27/10/2002
9
10	5	28/10/2002	29/10/2002	30/10/2002	31/10/2002	1/11/2002	2/11/2002	3/11/2002
11
12	6	4/11/2002	5/11/2002	6/11/2002	7/11/2002	8/11/2002	9/11/2002	10/11/2002
13
14	7	11/11/2002	12/11/2002	13/11/2002	14/11/2002	15/11/2002	16/11/2002	17/11/2002
15
16	8	18/11/2002	19/11/2002	20/11/2002	21/11/2002	22/11/2002	23/11/2002	24/11/2002
17
18	9	25/11/2002	26/11/2002	27/11/2002	28/11/2002	29/11/2002	30/11/2002	1/12/2002
19
20	10	2/12/2002	3/12/2002	4/12/2002	5/12/2002	6/12/2002	7/12/2002	8/12/2002
21
Sheet1

Paddy

 

[PaddyD]

Afterthoughts:

1) Notice that the formulas in col B are different to the rest
2) I didn't write all the formulas long-hand - just fill in the key ones & copy down / across as necessary...
3) You could add some conditional formatting / data validation to B2 to check that the date entered in B2 is a monday:

=weekday(b2)<>2

or similar.

Paddy

Paddy

 

[daleholden]

Thanks Paddy so simple how do i add the =weekday(b2)<>2 to B2 i have tried it but it still accepts an date. I tried going to conditional formatting and entering the formula.

I tried in the datevale formula but still not working.