Future date by adding number of days including all days except the days I specify.

  • I have looked for almost 2 hours over the internet for this solution but could not find one. Hopefully there's someone here with a solution.


    Summary:
    I have a date to which i am adding a number (of days) to arrive at a future date. I want all days to be counted including saturdays, sundays, holidays but I want only the dates that I have in another column to be excluded (because our holidays/days offs are spread out over the week and are very inconsistent BUT I do have the list of dates that should be skipped).


    Here's how I have it setup
    Sheet1 - A1 = 5 May 2011 (Date)
    Sheet1 - B1 = 10 (Number of days to add to above date to arrive at future date)
    Sheet1 - C1 = Formula needed here that adds A1+B1 (with all days that are in a column A in Sheet2 skipped till the total days equals 10). Another way to explain would be that the total days or actual workdays has to be 10 days skipping the days in the list. So in this example the answer needs to be 19th may.


    Sheet2 - beginning from A1 going down 1 day per row (thats all is in this column) This column is formatted date format and I will be adding new dates as they become available!


    7 may
    9 may
    10 may
    12 may


    Thanks!
    Flyboy

  • Re: Future date by adding number of days including all days except the days I specify


    I may just be short on imagination today, but I couldn't see a way to do that correctly/reliably with built-in Excel formulas. But was able to pretty quickly draw up a custom function that would do it. To make that work, just put the function in a module in your file. Make your list of dates to skip a named range (say CountOuts), then put this formula in C1: =DaysOut(A1,B1,CountOuts)

  • Re: Future date by adding number of days including all days except the days I specify


    From your profile I see you have excel 2007. For that version you could try this array formula in C1


    =A1+SMALL(IF(COUNTIF(Sheet2!A$1:A$100,A1+ROW(INDIRECT("1:"&B1*10))),"",ROW(INDIRECT("1:"&B1*10))),B1)


    confirmed with CTRL+SHIFT+ENTER


    In Excel 2010 there is an easier formula solution available by using the new WORKDAY.INTL function, i.e.


    =WORKDAY.INTL(A1,B1,"0000000",Sheet2!A$1:A$100)

  • Re: Future date by adding number of days including all days except the days I specify


    Thanks Barb-B. Works great for me.



    Quote from Barb-B;557305

    I may just be short on imagination today, but I couldn't see a way to do that correctly/reliably with built-in Excel formulas. But was able to pretty quickly draw up a custom function that would do it. To make that work, just put the function in a module in your file. Make your list of dates to skip a named range (say CountOuts), then put this formula in C1: =DaysOut(A1,B1,CountOuts)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!