Excel Calendar Values

MichelleC205 

You do not identify which version of Excel you are using. I'm going to assume you use a version that supports the LET function.

Using formulas only? Technically ... perhaps, barely, using the LET function. But that would require such very lengthy formulas that I would not even try.

But using VBA code (stored in a macro-enabled workbook which, as always, involves some security risk) along with formulas, definitely yes. It can be accomplished with or without the LET function, but using LET in my sample workbook allows me to write the formulas more plainly.

I wrote the custom VBA function CalcBusinessDay to perform the calculation. It takes parameters of

1. the From Date
2. the number of business days to count forward (15 in your case)
3. a range of holiday dates (in one column; I have not worked it out to accept all the dates in one row) Keeping the dates in an Excel table would probably be a better solution, but I'm not going to investigate the changes needed for that.

Public Function CalcBusinessDay(ByVal FromDate As Date _
        , ByVal NumOfBusDaysAhead As Integer _
        , Optional ByVal HolidayRange As Range _
        , Optional ByVal HolidayArray As Variant _
        ) As Variant
'   This procedure calculates and returns the next business day
'       (on* or) after the specified FromDate.  NumOfBusDaysAhead
'       can be a value up to 60.
'   The HolidayRange parameter takes precedence over the
'       HolidayArray parameter, if both are specified.
'   * Note that NumOfBusDaysAhead can be zero.  In that case,
'       if FromDate is a weekend or a holiday, the next business
'       day is returned; otherwise, FromDate is a business day and
'       it is returned.

    Dim dteReturnValue  As Date
    
    Dim vntCopy     As Variant  'a copy of the holiday date values
    Dim blnHasTwoDimensions As Boolean
    Dim in4LastItem As Long 'index of the last holiday date
    '
    Dim dteWorkDate     As Date 'This date starts at FromDate and _
            is incremented until the answer is found.
    Dim in2BusinessDayCount As Integer
    Dim in2Iteration    As Integer
    Dim vntItem     As Variant
    Dim in4HolidayIdx   As Long
    
    '----
    On Error GoTo DueDte_ErrHndlr
    
    '----   Validate input.
    If NumOfBusDaysAhead < 0 _
    Or NumOfBusDaysAhead > 60 Then
        CalcBusinessDay = CVErr(380)
        Exit Function
    End If
    '  --
    If Not HolidayRange Is Nothing Then
        vntCopy = HolidayRange
    ElseIf TypeName(HolidayArray) = "Variant()" Then
        vntCopy = HolidayArray
        blnHasTwoDimensions = UBound(HolidayArray, 2)
            '(That is a tricky way of verifying that the array has at least
            'two dimensions. If it does not, that test will cause an exception.)
        If UBound(HolidayArray, 2) > LBound(HolidayArray, 2) Then
            '...You've passed in a range with multiple columns.
            'Should I just ignore the extra data?  No; the person
            'writing that code is causing memory to be wasted.
            CalcBusinessDay = CVErr(380)
            Exit Function
        End If
    Else
        CalcBusinessDay = CVErr(380)
        Exit Function
    End If
    
    '----   To improve efficiency, identify the last entry among the
    '       values with a (presumed) date.
    in4LastItem = UBound(vntCopy, 1)
    Do
        vntItem = vntCopy(in4LastItem, 1)
        If VarType(vntItem) = vbDate _
        Or VarType(vntItem) = vbDouble Then
            Exit Do
        End If
        in4LastItem = in4LastItem - 1
    Loop
    
    '----
    dteWorkDate = FromDate
    '   The first pass through checking has a date offset of
    '   zero days from FromDate.
    Do
        in2Iteration = in2Iteration + 1
        '  --
        If Weekday(dteWorkDate) = vbSunday Then
            GoTo NextIteration
        ElseIf Weekday(dteWorkDate) = vbSaturday Then
            '   Skip Sunday as well.
            dteWorkDate = dteWorkDate + 1
            GoTo NextIteration
        End If
        '  --   Check the holidays to see if Work Date is present.
        '       (This is performed by an inefficient method, if the
        '       holidays are in chronological order.  But it's still fast.)
        For in4HolidayIdx = 1 To in4LastItem
            vntItem = vntCopy(in4HolidayIdx, 1)
            If VarType(vntItem) = vbDate _
            Or VarType(vntItem) = vbDouble Then
                If dteWorkDate = vntItem Then
                    '...Work Date is a holiday; skip it.
                    GoTo NextIteration
                End If
            Else
                'Ignore other data.
            End If
        Next in4HolidayIdx
        '  --
        If in2BusinessDayCount = NumOfBusDaysAhead Then
            dteReturnValue = dteWorkDate
            Exit Do
        End If
        in2BusinessDayCount = in2BusinessDayCount + 1
NextIteration:
        If in2Iteration > 125 Then
            '...something is amiss!
            CalcBusinessDay = CVErr(2001)
            Exit Function
        End If
        dteWorkDate = dteWorkDate + 1
    Loop
    
    '----
    CalcBusinessDay = dteReturnValue
    Exit Function

DueDte_ErrHndlr:
    Dim in4ErrorCode    As Long
    Dim strErrorDescr   As String
    '  --   Capture info.
    in4ErrorCode = Err.Number
    strErrorDescr = Err.Description
    '  --
    CalcBusinessDay = CVErr(in4ErrorCode)
    Exit Function
    Resume  'inaccessible, but retained for debugging
End Function

Put CalcBusinessDay into a standard code module. If you need an explanation of how to do that, just ask.

I started to structure some cells like your sample, but did not want to continue that, as it will take up so much space in my sample, making it harder to review the results. And yes, maybe you do not allow Lock Dates to occur on weekends or on holidays, but the code supports the calculation for those days also.

If the 3 shown as your Lock Date is actually a formatted date (showing only the day-of-month), you can use that cell as the first argument, no worries. But if the 3 is actually just a one- or two-digit number (or is text), you'll need a calculation to derive the actual date. I did that in three different ways on the attached workbook, for January, February, and March 2023.

Regarding the Holidays worksheet, the code does allow for blank cells embedded in the Dates column. A row does not affect the Due Date calculations unless its Date column contains a date. If you already have a worksheet/other workbook that contains holiday data, you can probably use it instead. Otherwise, you can add holidays for years further in the future (and the past), include your own company holidays, and if your company does not celebrate holidays like those included, those rows can be removed. In the Calendar worksheet formulas I wrote to use it, only the first ~333 rows are used, so someone will have to update those references ... in about 20 years. Feel free to change the $A$333 cell address before then.