Kelley day schedule help

[firefighter345]

I am a firefighter and work with excel a couple of times a year. One specific instance is I "build" our Kelley day schedule... it is a long painful process and it takes quite a bit of time. I am hoping someone can show, tell or teach me how to setup the document to autofill when one date is input. A little info may help this proceed and I will (if I can figure it out) post a screen shot of a current schedule.

So here is how it works we work every third day so I work Sunday, Wednesday and Saturday one week, Tuesday, Friday the next and then Monday, Thursday before it starts all over at Sunday. We are forced to take a day off every 6 weeks on a day we choose the year prior. So for example every 6th Monday in 2016 I have off and that started in January 4th so this year I had the following days off Jan 4, Feb 15, Mar 28, May 9, June 20, Aug 1, Spet 12, Oct 24 and Dec 5. I hope this makes sense. Also if it could auto total the number of days in the column that would be great, if not I can handle that quickly enough.

Our 2018 calendar is what I am working to solve the formula(s) for but don't know enough about excel to figure it out. It is or should be formatted identically to this...

[/URL][/IMG]

Thanks in advance for help/suggestions

The first Kelley of the year for my shift in 2018 is Tuesday January 2nd the next would be Friday January 5, Monday Jan 8, continuing every third day

 

[firefighter345]

Re: Hello

So I presume with the lack of response this is not possible?

 

[Perpa]

Re: Hello

firefighter345,

I'm afraid I have more questions than answers for this thread. First off, your title 'Hello' is not descriptive of what help you are seeking.
I am trying to understand the 2016 schedule you provided.
I assume a 'Kelley Day' is a Day Off.
You are not working Mondays beginning Jan 4th and every 6 weeks (42 days) thereafter.
Simple enough…C23=C22+42, then copy cell c23 and paste it down to C30.
I assume you were working Jan 1st, but off Jan 4th.

The rest is very confusing…assuming it takes 3 weeks to complete the rotation described:
Sunday, Wednesday, Saturday in Week 1
Tuesday and Friday in Week 2
Monday and Thursday in Week 3
Why are there only 2 weeks shown?

I tried making sense of it using a 3 week format, see spreadsheet excerpt below.
But I had only questions:
Jan 19th and 22nd are in the 4th week of the month.
Why are you showing Jan 19 in Week 1 and Jan 22 in Week 2?
Jan 25 and 28 are in the 5th week of the month.
Why are you showing Jan 25th and 28th in Week 2?
Perhaps you can provide some clarification on these questions and others might join in.
Perpa

Excel 2007

	A	B	C	D	E	F	G	H
18			2016 SCHEDULE
19			Days Off
20	Day/Week	Sunday/1	Monday/1	Tuesday/1	Wednesday/1	Thursday/1	Friday/1	Saturday/1
21	Name
22		10-Jan	4-Jan		13-Jan		1-Jan	16-Jan
23		31-Jan	15-Feb		3-Feb			6-Feb
24		21-Feb	28-Mar		24-Feb			27-Feb
25			9-May
26			20-Jun
27			1-Aug
28			12-Sep
29			24-Oct
30			5-Dec
31
32
33
34	Day/Week	Sunday/2	Monday/2	Tuesday/2	Wednesday/2	Thursday/2	Friday/2	Saturday/2
35	Name
36				19-Jan			22-Jan
37				9-Feb			12-Feb
38				1-Mar			4-Mar
39
40
41
42
43
44
45
46
47
48	Day/Week	Sunday/3	Monday/3	Tuesday/3	Wednesday/3	Thursday/3	Friday/3	Saturday/3
49	Name
50			25-Jan			28-Jan
51			15-Feb			18-Feb
52			7-Mar			10-Mar
53

<tbody>
</tbody>

Sheet1​

<tbody>
</tbody>

 

[Peter_SSs]

Re: Hello

Welcome to the MrExcel board!

Images of data are not as good as data that can be copy/pasted to our worksheets to test with so for the future, you might investigate: Attachments

I am also somewhat confused by the whole explanation, and particularly in relation to the forced days off. However, if what you have shown is what you want for 2016 then you could try this:

Start date is entered into cell A1
Formula in B3 is copied across to H3
Formula in B4 is copied across to H4 and down to row 11.
Formula in B12 is copied across to H12

Formula in B16 is copied across to H16
Formula in B17 is copied across to H17 and down to row 24
Formula in B25 is copied across to H25

Excel 2010 32 bit
	A	B	C	D	E	F	G	H
1	1-Jan-16
2		Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
3		10-Jan	4-Jan	19-Jan	13-Jan	7-Jan	1-Jan	16-Jan
4		21-Feb	15-Feb	1-Mar	24-Feb	18-Feb	12-Feb	27-Feb
5		3-Apr	28-Mar	12-Apr	6-Apr	31-Mar	25-Mar	9-Apr
6		15-May	9-May	24-May	18-May	12-May	6-May	21-May
7		26-Jun	20-Jun	5-Jul	29-Jun	23-Jun	17-Jun	2-Jul
8		7-Aug	1-Aug	16-Aug	10-Aug	4-Aug	29-Jul	13-Aug
9		18-Sep	12-Sep	27-Sep	21-Sep	15-Sep	9-Sep	24-Sep
10		30-Oct	24-Oct	8-Nov	2-Nov	27-Oct	21-Oct	5-Nov
11		11-Dec	5-Dec	20-Dec	14-Dec	8-Dec	2-Dec	17-Dec
12	# of Days	9	9	9	9	9	9	9
13
14
15		Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
16		31-Jan	25-Jan	9-Feb	3-Feb	28-Jan	22-Jan	6-Feb
17		13-Mar	7-Mar	22-Mar	16-Mar	10-Mar	4-Mar	19-Mar
18		24-Apr	18-Apr	3-May	27-Apr	21-Apr	15-Apr	30-Apr
19		5-Jun	30-May	14-Jun	8-Jun	2-Jun	27-May	11-Jun
20		17-Jul	11-Jul	26-Jul	20-Jul	14-Jul	8-Jul	23-Jul
21		28-Aug	22-Aug	6-Sep	31-Aug	25-Aug	19-Aug	3-Sep
22		9-Oct	3-Oct	18-Oct	12-Oct	6-Oct	30-Sep	15-Oct
23		20-Nov	14-Nov	29-Nov	23-Nov	17-Nov	11-Nov	26-Nov
24			26-Dec			29-Dec	23-Dec
25	# of Days	8	9	8	8	9	9	8
2016

Cell Formulas
Range	Formula
B3	=IF(TEXT($A1,"dddd")=B2,$A1,F3+3)
B4	=IF(YEAR(B3+42)=YEAR($A$1),B3+42,"")
B12	=COUNT(B3:B11)
B16	=B3+21
B17	=IF(YEAR(B16+42)=YEAR($A$1),B16+42,"")
B25	=COUNT(B16:B24)

Now, if I change cell A1 to 2-Jan-18, I get the following. Is that what you want for 2018? If not, more clarification please.

Excel 2010 32 bit
	A	B	C	D	E	F	G	H
1	2-Jan-18
2		Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
3		14-Jan	8-Jan	2-Jan	17-Jan	11-Jan	5-Jan	20-Jan
4		25-Feb	19-Feb	13-Feb	28-Feb	22-Feb	16-Feb	3-Mar
5		8-Apr	2-Apr	27-Mar	11-Apr	5-Apr	30-Mar	14-Apr
6		20-May	14-May	8-May	23-May	17-May	11-May	26-May
7		1-Jul	25-Jun	19-Jun	4-Jul	28-Jun	22-Jun	7-Jul
8		12-Aug	6-Aug	31-Jul	15-Aug	9-Aug	3-Aug	18-Aug
9		23-Sep	17-Sep	11-Sep	26-Sep	20-Sep	14-Sep	29-Sep
10		4-Nov	29-Oct	23-Oct	7-Nov	1-Nov	26-Oct	10-Nov
11		16-Dec	10-Dec	4-Dec	19-Dec	13-Dec	7-Dec	22-Dec
12	# of Days	9	9	9	9	9	9	9
13
14
15		Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
16		4-Feb	29-Jan	23-Jan	7-Feb	1-Feb	26-Jan	10-Feb
17		18-Mar	12-Mar	6-Mar	21-Mar	15-Mar	9-Mar	24-Mar
18		29-Apr	23-Apr	17-Apr	2-May	26-Apr	20-Apr	5-May
19		10-Jun	4-Jun	29-May	13-Jun	7-Jun	1-Jun	16-Jun
20		22-Jul	16-Jul	10-Jul	25-Jul	19-Jul	13-Jul	28-Jul
21		2-Sep	27-Aug	21-Aug	5-Sep	30-Aug	24-Aug	8-Sep
22		14-Oct	8-Oct	2-Oct	17-Oct	11-Oct	5-Oct	20-Oct
23		25-Nov	19-Nov	13-Nov	28-Nov	22-Nov	16-Nov	1-Dec
24			31-Dec	25-Dec			28-Dec
25	# of Days	8	9	9	8	8	9	8
2018

 

[firefighter345]

Re: Hello

However, if what you have shown is what you want for 2016 then you could try this:

This sure seems to be what I am looking for. I will look it over and get back to you.

perpa I am not sure what your set up is

 

[Peter_SSs]

Re: Hello

This sure seems to be what I am looking for. I will look it over and get back to you.

OK, no problem.

BTW, you may have noticed that I removed most of my message that you quoted in your post. It is best not to quote whole long posts as it makes your post and the whole thread harder to read/navigate. If you need to quote, just quote enough to indicate who you are replying to and/or any specific parts of the post that you want to refer to.


Edit: I also just realised that I didn't give you all the formulas/information needed for my solution. In the first table, the first row requires two different formulas due to the 'wrap around' effect, as follows:

Formula in B3 is copied across, only as far as D3
Formula in E3 is copied across to H3

Excel 2010 32 bit
	A	B	C	D	E	F	G	H
1	1-Jan-16
2		Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
3		10-Jan	4-Jan	19-Jan	13-Jan	7-Jan	1-Jan	16-Jan
2016

Cell Formulas
Range	Formula
B3	=IF(TEXT($A1,"dddd")=B2,$A1,F3+3)
E3	=IF(TEXT($A1,"dddd")=E2,$A1,B3+3)

 

[firefighter345]

So just to be sure the formula is copied and pasted exactly as it is into those cells indicated? I am having to change your values to match the cells in the spreadsheet we already have built so I am a bit confused.

ALso you said that your B16 is copied across to H16 so each one should be the formula you have in B16 or the B needs to change with each column?

 

[Peter_SSs]

Re: Hello

So just to be sure the formula is copied and pasted exactly as it is into those cells indicated?

Yes, copy the formula from the forum and paste into the cell indicated. Then copy/paste (or drag the Fill Handle at the bottom right of the cell) into the other cells indicated.

However, the formula is written for my layout (obviously ), not yours.

I am having to change your values to match the cells in the spreadsheet we already have built so I am a bit confused.

If possible, I'd suggest replicating my layout to 'prove' the concept before trying to modify to suit your layout.

In the end, if you need help with the modifications, then show/tell us more detail about your layout.

 

[Peter_SSs]

Re: Hello

ALso you said that your B16 is copied across to H16 so each one should be the formula you have in B16 or the B needs to change with each column?

The B will change. You copy my formula into the cell indicated, then you copy that cell to the other cells. In doing that, the references will change automatically.