i have to generate a vacation calendar for my department and i startet with some formulas to generate a unique list of vacation days for every team member via name manager. My problem now is, that the vacation list should only list the absent workdays between two dates, including start and end date.
To make it more comfortable, i took a little vba code, but it lists all days between two dates. How do i have to adopt it to list only the working days between the dates?
Code: Select all
Sub WriteVacation()
'UpdatebyStefan
Dim rng As Range
Dim StartRng As Range
Dim EndRng As Range
Dim OutRng As Range
Dim StartValue As Variant
Dim EndValue As Variant
xTitleId = "Vacation List"
Set StartRng = Application.Selection
Set StartRng = Application.InputBox("Start Range (single cell):", xTitleId, StartRng.Address, Type:=8)
Set EndRng = Application.InputBox("End Range (single cell):", xTitleId, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set OutRng = OutRng.Range("A1")
StartValue = StartRng.Range("A1").Value
EndValue = EndRng.Range("A1").Value
If EndValue - StartValue <= 0 Then
Exit Sub
End If
ColIndex = 0
For i = StartValue To EndValue
OutRng.Offset(ColIndex, 0) = i
ColIndex = ColIndex + 1
Next
End Sub