Creating date sequence in Excel 97

[sgmacs]

I've run out of ideas about how to create a date sequence for a work rota. It's a recurring weekly pattern of five dates and then two dates doubled, e.g.

Jul/01/04
Jul/02/04
Jul/03/04
Jul/04/04
Jul/05/04
Jul/06/04
Jul/06/04
Jul/07/04
Jul/07/04

This is followed by a gap, and then the pattern repeats ad infinitum. Currently I do this manually, and it's terribly tedious.

Any suggestions gratefully received.

 

[Aladin Akyurek]

Try to prevent wild guesses on the part of the would be helpers by specifying the rule how you create the sequence. Anything special about the start date? Do the week end dates play any special role, etc.?

 

[sgmacs]

No, it's very simple. The first date is a Monday, the last two are Saturday and Sunday split into two shifts.

 

[Aladin Akyurek]

No, it's very simple. The first date is a Monday, the last two are Saturday and Sunday split into two shifts.

1-July-04 is a Thursday...

Book4
	A	B	C	D
1	DateSeq
2	Mon5-Jul-04
3	Tue6-Jul-04
4	Wed7-Jul-04
5	Thu8-Jul-04
6	Fri9-Jul-04
7	Sat10-Jul-04
8	Sat10-Jul-04
9	Sun11-Jul-04
10	Sun11-Jul-04
11	Mon12-Jul-04
12	Tue13-Jul-04
13	Wed14-Jul-04
14	Thu15-Jul-04
15	Fri16-Jul-04
16	Sat17-Jul-04
17	Sat17-Jul-04
18	Sun18-Jul-04
19	Sun18-Jul-04
Sheet1

The formula in A3 is:

=A2+IF(OR(WEEKDAY(LOOKUP(9.99999999999999E+307,$A$2:A2),2)={6,7}),0,1)+(A2=A1)

 

[sgmacs]

That's great. Thanks very much.

 

[Yogi Anand]

Hi sgmacs:

Or you may also try ...

y031221h1.xls
	A	B	C	D
1
2	05-Jul-04
3	06-Jul-04
4	07-Jul-04
5	08-Jul-04
6	09-Jul-04
7	10-Jul-04
8	10-Jul-04
9	11-Jul-04
10	11-Jul-04
11	12-Jul-04
12	13-Jul-04
13	14-Jul-04
14	15-Jul-04
15	16-Jul-04
16	17-Jul-04
17	17-Jul-04
18	18-Jul-04
19	18-Jul-04
20	19-Jul-04
21	20-Jul-04
22	21-Jul-04
23	22-Jul-04
24	23-Jul-04
25	24-Jul-04
Sheet2 (2)

Cell A2 houses the starting date, then the formula in cell A3 is ...

=A2+(MOD(A2,7)>1)+(A2=A1)

and this is then copied down to as many cells as desired.