“I need a vacation.” [Arnold Schwarzenegger as T-800 in Terminator 2]


Each year we members of the working population are asking ourselves when we should take our vacation.

This program shows you the maximal achievable number of free days for your given vacation days:


Example: With the given bank holidays in 2024 and just 7 days of vacation you can get 16 free days from 14-Dec-2024 until 29-Dec-2024. You cannot achieve more in 2024, as the second best solution proves (that will result in 14 free days only).

Appendix – sbOptimalVacationDays Code

Please read my Disclaimer.

Option Explicit

Function sbOptimalVacationDays(lNumberOfVacationDays As Long, _
    dtStartDate As Date, _
    dtEndDate As Date, _
    vBankHolidays As Variant) As Variant
'This function informs you for all days from
'1..lNumberOfVacationDays which exact days would result in the
'longest (as well as second longest) series of subsequent free
'days with given vBankHolidays and assuming that weekends
'(Saturdays and Sundays) are also free.
'Source (EN): http://www.sulprobil.com/sboptimalvacationdays_en/
'Source (DE): http://www.bplumhoff.de/sboptimalvacationdays_de/
'(C) (P) Bernd Plumhoff v0.2 07-Jan-2024
Dim dt      As Date
Dim dtMax   As Date
Dim i       As Long
Dim j       As Long
ReDim v(1 To lNumberOfVacationDays, 1 To 7) As Variant
With Application.WorksheetFunction
If lNumberOfVacationDays < 1 Then
    sbOptimalVacationDays = CVErr(xlErrValue)
    Exit Function
End If
If dtStartDate > dtEndDate Then
    sbOptimalVacationDays = CVErr(xlErrNum)
    Exit Function
End If
For i = 1 To lNumberOfVacationDays: v(i, 1) = i: Next i
For dt = dtStartDate To dtEndDate
    For i = 1 To lNumberOfVacationDays
        dtMax = .WorkDay(.WorkDay(dt - 1, 1, _
                vBankHolidays), i, vBankHolidays) - 1
        If dtMax > dtEndDate Then Exit For
        j = dtMax - dt + 1
        If j >= v(i, 2) Then
            v(i, 7) = v(i, 4)
            v(i, 6) = v(i, 3)
            v(i, 5) = v(i, 2)
            v(i, 2) = j
            v(i, 3) = dt
            v(i, 4) = dtMax
        End If
    Next i
Next dt
sbOptimalVacationDays = v
End With
End Function


Please read my Disclaimer.

sbOptimalVacationDays.xlsm [21 KB Excel file, open and use at your own risk]