complicated date formula

methody

Well-known Member
Joined
Jun 17, 2002
Messages
845
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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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

Well-known Member
Joined
Jun 17, 2002
Messages
845
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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
 

methody

Well-known Member
Joined
Jun 17, 2002
Messages
845

ADVERTISEMENT

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

methody

Well-known Member
Joined
Jun 17, 2002
Messages
845
sorry fairwinds

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

Forum statistics

Threads
1,136,347
Messages
5,675,233
Members
419,555
Latest member
Paddington

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top