+ Reply to Thread
Results 1 to 3 of 3

Need help working around a holiday

  1. #1
    Registered User
    Join Date
    12-17-2009
    Location
    Prattville,Alabama
    MS-Off Ver
    2010
    Posts
    4

    Need help working around a holiday

    Okay I will try my best to not make this description a convoluted mess. My company has stopped disconnecting services on Fridays. Prior to the change the disconnection date was current date + 2 business days. While that still remains the same I now need to exclude Friday as well. Thus far I have come up with =IF(C52=6,WORKDAY(I30,3,holidays),WORKDAY(I30,2,holidays)).

    ** Where C52 is simply a reference to signify the day of the week and I30 is the current days date** Basically it is stating that if today is Wednesday make the disconnect date 3 business days and any other time make it two while accounting for holidays.

    Well this actually works perfectly until you hit a holiday that lands on a Thursday, such as July 4th this year or Thanksgiving. On those days it pushes the date one day further than I need. To combat that I removed the holiday schedule for the "true" value and that solves the Thursday holiday problem but in return it doesn't work for Monday holidays.. After mulling over this and multiple face palms I am now surrendering and hoping that someone here can help me. Any help will be greatly appreciated and I thank you in advance for your time.

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Need help working around a holiday

    Well, my best offering is to suggest throwing fridays of the current year into the holiday list...without seeing something of how your data is set up, it's very hard to give you a solution for this though...
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  3. #3
    Registered User
    Join Date
    12-17-2009
    Location
    Prattville,Alabama
    MS-Off Ver
    2010
    Posts
    4

    Re: Need help working around a holiday

    The data is set up pretty simple:

    Notice Date - current days date
    Disconnect Date - + two business days (The catch is that the disconnect date cannot fall on a Friday or a holiday.)

    As I stated in the original post, =IF(C52=6,WORKDAY(I30,3,holidays),WORKDAY(I30,2,holidays)) works perfectly unless of course there is a Thursday holiday. On a Thursday holiday it pushes the disconnect date one day too far. As an example, when I reach Wednesday July 3rd, 2013 my disconnect date needs to be Monday July 8th but because of the holiday on Thursday the 4th my disconnect date will push to Tuesday July 9th.

    Is there a way to evaluate the conditions and perhaps have a nested if that states something like "if you encounter a holiday when adding the business days, subtract one from the final answer". Or better yet, is there a way to allow this formula to work then simply evaluate the final answer and make sure it hasn't landed on a holiday? If I can do this then I can remove the holiday schedule from the true value and this formula would actually work. I would need to ensure that Monday wasn't a holiday though if I went that route. Anyway, Im babbling now.. Thanks in advance for any time or effort you can provide in helping!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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