Search code examples
vbaexcelexcel-formulaexcel-2013

How to skip user chosen dates in MS Excel autofill?

I am given a start date that I enter into a cell. Then I do a fill down 100 days. It then sequentially will fill in all the days. There is a setting to exclude weekends which I applied. So now it is the next 100 weekdays. The problem I have is that I need to be able to skip other holidays. For example what if I want to skip 7/4/2014 which was a Friday? I can use VBA if necessary.

Solution

  • Put the start date in A1, e.g. for this financial year:-

    4/1/2014

    and the list of dates you want to skip in column B starting in B1, e.g. (as in the question)

    7/4/2014

    and put the formula using WORKDAY as suggested in A2

    =WORKDAY(A1,1,B$1:B$1)

    and pull the formula down as required.

    As a Brit, I've just realised that 7/4/2014 means Fourth of July if the dates are in MM/DD/YYYY format.