# Thread: Workday function to find specific date ?

1. Registered User
Join Date
Nov 2009
Location
London
Posts
3

## Unanswered: Workday function to find specific date ?

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

2. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
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:
Code:
`=IF(WORKDAY(G3-1,1,MyHolidays)=G3,G3,WORKDAY(G3,-1,MyHolidays))`
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..

3. Registered User
Join Date
Nov 2009
Location
London
Posts
3
Morning Colin and thanks for the welcome !
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

4. Registered User
Join Date
Nov 2009
Location
London
Posts
3
Originally Posted by Nongeek
Many thanks, will report back later......
........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

5. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Great stuff, Steve. Let us know if you get stuck with anything else....

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•