complicated date formula

[methody]

Hello there
I have a set of sports fixtures and I have a brilliant formula (provided by Fairwinds on this messageboard) to the left of each fixture which automatically calculates the date given the start date of season, the frequency of the matches and including free dates:

the formula is IF(OR($Q$5="",H7=""),"",IF($R$5=14,SMALL(IF(ISNA(MATCH(ROW($1:$200)*$R$5-$R$5+$Q$5,$S$5:$S$14,0)),(ROW($1:$200)*$R$5-$R$5+$Q$5),""),H7),SMALL(IF(ISNA(MATCH(ROW($1:$200)+$Q$5-1,$S$5:$S$14,0))*ISNUMBER(MATCH(WEEKDAY(ROW($1:$200)+$Q$5-1,2),$R$5:$R$7,0)),(ROW($1:$200)+$Q$5-1),""),H7))) entered as an array
Q5 is the start date
R5 is an indicator of frequency
S5 to S14 are the free dates

I am trying to amend the formula even more to allow extra dates to be included so that most games would be perhaps on a Saturday but there may be a particular date, perhaps a bank holiday Monday when another batch of games could be included.
I know its a bit of an 'ask' but any help would be appreciated.

 

[fairwinds]

Hi,

=IF(OR($Q$5="",H7=""),"",IF($R$5=14,SMALL(IF(ISNA(MATCH(ROW($1:$200)*$R$5-$R$5+$Q$5,$S$5:$S$14,0)),(ROW($1:$200)*$R$5-$R$5+$Q$5),""),H7),SMALL(IF(ISNA(MATCH(ROW($1:$200)+$Q$5-1,$S$5:$S$14,0))*(ISNUMBER(MATCH(WEEKDAY(ROW($1:$200)+$Q$5-1,2),$R$6:$R$7,0))+ISNUMBER(MATCH(ROW($1:$200)+$Q$5-1,$T$5:$T$14,0))>0),ROW($1:$200)+$Q$5-1,""),H7)))

Should include any extra dates specified in T7:T14.

This does not apply if you specify 14 in R5.

Ctrl + Shift + Enter!

 

[methody]

Hello ther eFairwinds

Great to have you back on the case but this is not quite giving me what I need.
I have a start of season date 1st January so the original formula then inputs 1st January for the first set of matches and 8th of January for the second set of matches unless there was a free date in which case it would have been 15th January.
If an extra date is put in T5:T14, for example 3rd January. I want the first batch to be 1st January and the 2nd batch should all be 3rd January and the next batch to be 8th January
Perhaps I am not entering it correctly

 

[fairwinds]

As far as I can tell, it does exactly that.

Book1
	H	I	J	K	L	M	N	O	P	Q	R	S	T
4	match	date	home team		away team					Season start	Match days	Free dates	Extra dates
5	1	2004-01-01	Liverpool	v	celtic					2004-01-01		2004-09-16	2004-01-03
6	1	2004-01-01	Barcelona	v	Dundonald Boys						2	2004-09-21
7	1	2004-01-01	Chelsea	v	Leeds						4
8	1	2004-01-01	Arsenal	v	Rangers
9	2	2004-01-03	Dundonald Boys	v	Liverpool
10	2	2004-01-03	Rangers	v	Chelsea
11	2	2004-01-03	Leeds	v	Barcelona
12	2	2004-01-03	celtic	v	Arsenal
13	3	2004-01-06	Liverpool	v	Leeds
14	3	2004-01-06	celtic	v	Dundonald Boys
15	3	2004-01-06	Barcelona	v	Rangers
16	3	2004-01-06	Arsenal	v	Chelsea
17	4	2004-01-08	Rangers	v	Liverpool
Sheet2

 

[methody]

thanks for trying but that not it. Don't worry about
thanks

 

[fairwinds]

thanks for trying but that not it. Don't worry about
thanks

 

[methody]

sorry fairwinds

should not have doubted you. Brilliant I was making a mistake in the R column