Results 1 to 5 of 5
  1. #1
    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. #2
    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. #3
    Join Date
    Nov 2009
    Location
    London
    Posts
    3
    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

  4. #4
    Join Date
    Nov 2009
    Location
    London
    Posts
    3
    Quote Originally Posted by Nongeek View Post
    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. #5
    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
  •