+ Reply to Thread
Results 1 to 6 of 6

Bank Holidays when BH is day 1

  1. #1
    Registered User
    Join Date
    04-07-2010
    Location
    Ampthill, England
    MS-Off Ver
    Excel 2007 Home 2003 Work
    Posts
    4

    Bank Holidays when BH is day 1

    Hi
    I have searched forums but to no avail at the moment.
    I am trying to have a formula that will show the date for a mailing to be sent out. Normally the day in is day out unless it is a Saturday or Sunday when it will be Monday. So far so good. Problem is if the day is a bank holiday eg Good Friday. My formula still sends out the same day. I need it to go to the Tuesday, ie day after Easter Mondya. Formula is as follows - "holidays" is the array with holiday dates in. thank you in advance

    Dave

    =IF(ISBLANK(E200),"",IF(WEEKDAY(E200,2)<6,E200,WORKDAY(E200,1,holidays)))

    E200 is date received.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Bank Holidays when BH is day 1

    I would suggest perhaps trying:

    =IF(E200="","",WORKDAY(E200-1,1,holidays))

    (edit: previously missing the blank/null test)
    Last edited by DonkeyOte; 04-07-2010 at 09:13 AM.

  3. #3
    Registered User
    Join Date
    04-07-2010
    Location
    Ampthill, England
    MS-Off Ver
    Excel 2007 Home 2003 Work
    Posts
    4

    Re: Bank Holidays when BH is day 1

    Thanks Donkeyote but if 2/4/10 put in ie Good Friday, date out is still 2/4/10 and not 6/4/10

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Bank Holidays when BH is day 1

    If the input is 2nd April 2010 the formula provided will output 6th April 2010.

    The key is that the formula looks for the first working day that occurs after the day before the arrival date.

  5. #5
    Registered User
    Join Date
    04-07-2010
    Location
    Ampthill, England
    MS-Off Ver
    Excel 2007 Home 2003 Work
    Posts
    4

    Re: Bank Holidays when BH is day 1

    OOops. Sorry didn't read reply properly! It works. must have been me flailing at windmills. many thanks!!

  6. #6
    Registered User
    Join Date
    04-07-2010
    Location
    Ampthill, England
    MS-Off Ver
    Excel 2007 Home 2003 Work
    Posts
    4

    Re: Bank Holidays when BH is day 1

    Also DonkeyOte thanks for shortening the formula. Much easier to understand.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1