Having problem work on a date to work on 1 to 18 week

[micope21]

Hi Guy.

I'm work on formula and I can't work out to get date. I'm not that good on date. Can you help.

I'm try get formula work on Date Yellow Column I:I. Need get formula work to get a Date. Look below to work with
Season Start = 13/10/2011
Freq = 4
No Match Day = 03/11/2011, 17/11/2011 & 22/12/2011
Day = Thursday
Week = 1 to 18
New Date & Cancelled

Look at test file.

[Special-K]

In words, what are you trying to do?

[micope21]

Word is

Need formula on Season Date, Freq, No match Day, Day, Week and New date & Cancelled.

I' m more in excel 2003 code. Now I got excel 2010 for 4 month now. There lot to learn new code on excel 2010. Formula I have try is this
=IF($F5<>"",IF(OR($O$5="",E5=""),"",IF($P$5=14,SMALL(IF(ISNA(MATCH(ROW($1:$200)*$P$5-$P$5+$O$5,$Q$5:$Q$14,0)),(ROW($1:$200)*$P$5-$P$5+$O$5),""),E5),SMALL(IF(ISNA(MATCH(ROW($1:$200)+$O$5-1,$Q$5:$Q$14,0))*(ISNUMBER(MATCH(WEEKDAY(ROW($1:$200)+$O$5-1,2),$P$5,0),""),E5)))

It not working. I spent all day change code. Nothing at the moment.
Hopeful some guy out there knew where I gone wrong.

[Haseeb Avarakkan]

I am not sure I fully understand. The attached basically doing this.

Return all the day specified in R3, start date in O3 & skip dates in Q5:Q7. If F5:F111 contains a value put them in corresponding cell in G5:G111.

Since you are on XL2010 used WORKDAY.INTL function. O7:P15 are helper to weekend array in WORKDAY.INTL. 1 means non-workday 0 means workday.

See the attached. Hope this helps.

[micope21]

Hi
Haseeb A

That new.
You done it right. nearly there.

You done G5 date on. But need G6, G7 ,G8, G9 and rest to 18 all need date on. Not blank.

[micope21]

Haseeb A

Something wrong on G23 on Number 4. I explain in the date to work on 1 to 18 week file

[Haseeb Avarakkan]

Try this,

=IF(E5="","",IF(F5<>"",F5,WORKDAY.INTL(O$5-1,E5,O$15,Q$5:Q$7)))

[micope21]

Try this,

=IF(E5="","",IF(F5<>"",F5,WORKDAY.INTL(O$5-1,E5,O$15,Q$5:Q$7)))

Haseeb A

Brilliant code. Look very simple. It work with all date. Just that I'm not good on date code. I change No match day to Q5:Q10. Some season it not just 3 sometime 4 up to 6.

Thank you very much for your brilliant code.

You take care mate
Problem Solved

Cheer