Forum Discussion
Excel Calendar Values
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
- the From Date
- the number of business days to count forward (15 in your case)
- 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 FunctionPut 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.