Excel and working days

I wanted a way to calculate in Excel what the date is of a day 12 working days from now. There has to be an easy way of doing this. However until I find it, here is my work around.

Function AddWorkingDays(startDate As Date, days As Integer) As Date

    If (Weekday(startDate) = vbSunday) Then

        days = days - 1 ' treat sunday like saturday

    End If

   

    AddWorkingDays = startDate + days + (NumberOfWeekends(startDate, days) * 2)

End Function



Function NumberOfWeekends(d, days)

    numDaysPastMonday = Weekday(d, vbMonday) - 1

    numDaysStartingFromACompleteWeek = days + numDaysPastMonday

    NumberOfWeekends = Application.WorksheetFunction.RoundDown(numDaysStartingFromACompleteWeek / 5, 0)

End Function

I even did unit tests when developing this marco. I like how easy this is in Excel. I made a sheet with the columns for the parametes 'startDate' and 'numDays', followed by 'Actual', 'Expected' and 'Result'.

Actual was using the method under test, so in this case '=AddWorkingDays($A2,$B2)'. Result was just a simple comparison formula '=($C2=$D2)'. In Excel you can put conditional formatting on a cell, so I also made the Results column change green on 'TRUE' and red on 'FALSE'. At the
bottom I put a 'Tests failing :' cell with a formula of '=COUNTIF(E1:E48,FALSE)' to summise all my tests. This too was conditionally formatted to red if greater than 0.

The only thing I didn't find a nice way of doing was refreshing the tests when the macro has changed. What I ended up doing was cutting and pasting one of the input columns. I am sure I could have written a macro attached to a button, but that was too much hastle. If anyone knows a better way, let me know :)

NOTE: I have been told of a better way.. I may update this post later to show it. If you can't wait, email me.