# complicated date formula

#### methody

##### Well-known Member
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.

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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!

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

As far as I can tell, it does exactly that.
Book1
HIJKLMNOPQRST
4matchdatehome teamaway teamSeason startMatch daysFree datesExtra dates
512004-01-01Liverpoolvceltic2004-01-012004-09-162004-01-03
612004-01-01BarcelonavDundonald Boys22004-09-21
712004-01-01ChelseavLeeds4
812004-01-01ArsenalvRangers
922004-01-03Dundonald BoysvLiverpool
1022004-01-03RangersvChelsea
1122004-01-03LeedsvBarcelona
1222004-01-03celticvArsenal
1332004-01-06LiverpoolvLeeds
1432004-01-06celticvDundonald Boys
1532004-01-06BarcelonavRangers
1632004-01-06ArsenalvChelsea
1742004-01-08RangersvLiverpool
Sheet2

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

sorry fairwinds

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

Replies
7
Views
245
Replies
1
Views
212
Replies
6
Views
258
Replies
1
Views
232
Replies
1
Views
251

1,218,945
Messages
6,145,359
Members
450,611
Latest member
JodiWe

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back