Hello All !

A newbie here obviously due to needing HELP!!!
I have genuinely spent hours trying to solve this problem until my brain hurts
I have been trying to get into Excel and this is my first hurdle:
I'd like to specify the same date each month EXCEPT when it falls on a weekend or Holiday, in which case I'd like it to show the first working day before the 20th, example: if I want to return the 20th of each month but it falls on a Sunday (19th is a Saturday and Friday 18th is a Holiday), I would like the result to show 17/5/2009.
I already have a named range to take the Holiday dates into the formula and have tried various combinations of Workday,Networkdays, Datedif functions.
The only formula I could get close was: =Workday(Field containing 1/1/2009),20,Holidays)...but this doesn't work.

I throw myself at your mercy !

Steve

Welcome to the forum.

Have one column with your unmodified dates in. Let's say this is G3:G100.

To then calculate the corresponding modified dates in column H, put this formula in H3 and copy down the column:
Where MyHolidays is a defined range containing your weekday holiday dates.


Prior to XL2007, to use the WORKDAY() function you must have the analysis toolpak installed.

Hope that helps..

Morning Colin and thanks for the welcome !
But more importantly thanks for your reply......
Not long got up, its cold, damp, still slightly dark but your reply was like my very own little bit of sunshine !
Will get around to trying it later once I've got my act together and I'm not ashamed to admit that I'm quite looking forward to it !
Oh dear ! Did I just say the highlight of my day is going to be an Excel formula ?.......Yeah !

Many thanks, will report back later......

Steve

........and I'm VERY pleased to report that it worked a treat, I am now a very happy bunny !
Thanks for your expertise Colin, it's very much apprciated, hurdle now cleared and I can now continue with my work.
Needless to say you are at the very top of my Christmas card list (above family & friends).....

Many grateful thanks....

Steve

Great stuff, Steve. Let us know if you get stuck with anything else....