tracyms
asked on
Holidays Between 2 Dates
I found a bit of vb code that’s close to what I need here:
https://www.experts-exchange.com/questions/23195737/Extracting-or-Calculating-the-working-days-between-two-dates-for-Options-valuation.html
I’ve listed it below but I’d like change it so that instead of returning a list dates in between the start and end dates, I want to return the list of holidays between the start and ends dates. I have a list of holiday dates in Row Y.
https://www.experts-exchange.com/questions/23195737/Extracting-or-Calculating-the-working-days-between-two-dates-for-Options-valuation.html
I’ve listed it below but I’d like change it so that instead of returning a list dates in between the start and end dates, I want to return the list of holidays between the start and ends dates. I have a list of holiday dates in Row Y.
Private Sub CommandButton1_Click()
startdate = Range("F11").Value
enddate = Range("G11").Value
daysbetween = enddate - startdate
Results = "Day 1" & vbTab & startdate & vbCrLf
For i = 1 To daysbetween
nextdate = startdate + i
Results = Results & "Day " & i & vbTab & nextdate & vbCrLf
Next i
MsgBox Results
End Sub
ASKER
I moved my holidays to row ak, starting in row ak2. I used the code below but it only shows the start date:
Private Sub CommandButton1_Click()
startdate = Range("F11").Value
enddate = Range("G11").Value
daysbetween = enddate - startdate
Results = "Day 1" & vbTab & startdate & vbCrLf
For Each cel In Range("ak2:ak" & Range("ak" & Rows.Count).End(xlUp).Row)
If cel.Value >= startdate And cel.Value <= enddate Then
Results = Results & "Day " & cel.Value - startdate & vbTab & nextdate & vbCrLf
End If
Next cel
MsgBox Results
End Sub
Private Sub CommandButton1_Click()
startdate = Range("F11").Value
enddate = Range("G11").Value
daysbetween = enddate - startdate
Results = "Day 1" & vbTab & startdate & vbCrLf
For Each cel In Range("ak2:ak" & Range("ak" & Rows.Count).End(xlUp).Row)
If cel.Value >= startdate And cel.Value <= enddate Then
Results = Results & "Day " & cel.Value - startdate & vbTab & nextdate & vbCrLf
End If
Next cel
MsgBox Results
End Sub
Can you upload a small sample excel file for testing?
ASKER
I've attached a test file.
holidays.xlsm
holidays.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ssaqibh,
I've changed my request - if that's okay. Please see attached spreadsheet for details.
holidaysRevisited.xlsm
I've changed my request - if that's okay. Please see attached spreadsheet for details.
holidaysRevisited.xlsm
This new request is significantly differently from the original request.
If the original request is appropriately answered the you should close this question and open another with the new request.
If there is something lacking in the solution with respect to the original request then continue to post here.
If the original request is appropriately answered the you should close this question and open another with the new request.
If there is something lacking in the solution with respect to the original request then continue to post here.
for each cel in range("y1:y"& range("y" & rows.count).end(xlup).row)
if cel.value >= startdate and cel.value <= enddate then
Results = Results & "Day " & cel.value - startdate & vbTab & nextdate & vbCrLf
endif
next cel