WORKDAY question

[KevHardy]

I have used the workday function to populate column A with just workdays
(also used a named list of holidays to exclude).
I was wondering if anyone had a bit of vba to automate this process?

 

[Chip Pearson]

You can use code similar to the following.

Sub AAA()
Dim R As Range
Dim StartDate As Date
Dim NumDays As Long
Dim N As Long
Dim Holidays() As Variant

Holidays = Range("E1:E10") '<<< CHANGE
StartDate = DateSerial(2010, 1, 1) '<<< CHANGE
NumDays = 30 '<<< CHANGE
Set R = Range("A1") '<<< CHANGE

On Error Resume Next
For N = 0 To NumDays
If Weekday(StartDate + N, vbMonday) <= 5 Then
If IsError(Application.Match(StartDate + N, Holidays, 0))
Then
R.Value = StartDate + N
Set R = R(2, 1)
End If
End If
Next N
End Sub

Change the lines marked with <<< to your values. Holidays is the range
that contains the holidays to exclude, StartDate is the first date at
which the list is to begin. NumDays is the number of weekdays to
include in the list, and R is the cell in which the list is to begin.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Mike H]

Hi

Change Sht to the sheet you want and manually populate your holidays in the
range E1 - E10 then run the code and you get your list of workdays in col A.
They will start from the date after the variable MyDate

Sub MyMacro()
Set Sht = Sheets("Sheet1")
Dim MyDate As Date
MyDate = #12/31/2009#
For x = 1 To 365
Do
Sht.Cells(x, 1) = WorksheetFunction.WorkDay(MyDate, 1)
If WorksheetFunction.CountIf(Sht.Range("E1:E10"), Sht.Cells(x, 1)) > 0 Then
MyDate = MyDate + 1
End If
Loop Until WorksheetFunction.CountIf(Sht.Range("E1:E10"), Sht.Cells(x, 1)) = 0
MyDate = Sht.Cells(x, 1).Value
Next
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

Just spotted a bug in that try this instead

Sub MyMacro()
Set Sht = Sheets("Sheet1")
Dim MyDate As Date
MyDate = #12/31/2009#
For x = 1 To 365
Do
Sht.Cells(x, 1) = WorksheetFunction.WorkDay(MyDate, 1)
If WorksheetFunction.CountIf(Sht.Range("E1:E10"), Sht.Cells(x, 1)) > 0 Then
Sht.Cells(x, 1) = WorksheetFunction.WorkDay(Sht.Cells(x, 1), 1)
End If
Loop Until WorksheetFunction.CountIf(Sht.Range("E1:E10"), Sht.Cells(x, 1)) = 0
MyDate = Sht.Cells(x, 1).Value
Next
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Rick Rothstein]

Here is another approach for you to consider (just set the start and finish dates and the holiday named range as needed)...

Sub PopulateWorkdays()
Dim X As Long, Z As Long, Start As Date, Finish As Date, H() As String
H = Split(Join(WorksheetFunction.Transpose(Range("Holidays"))))
Start = DateSerial(2010, 1, 1)
Finish = DateSerial(2010, 12, 31)
For X = Start To Finish
If Weekday(X, vbMonday) < 6 And UBound(Filter(H, X)) < 0 Then
Z = Z + 1
Cells(Z, "A").Value = X
End If
Next
End Sub

 

[KevHardy]

Thanks Mike, Chip and Rick
It's interesting to see three versions of tackling the same task - and the
notes made it much easier to see what's going on.
Thanks a million

 

[KevHardy]

Hi Rick,
How would I change your code to allow the user to enter a start date and end
date? i.e. the Start and Finish would be from cell contents (G2 and G3
perhaps) rather than DateSerial().
I've tried changing this myself but don't really know what I'm doing

I'm presuming the Dim will be different (Range rather than Date?)

 

[Rick Rothstein]

No, assuming G2 and G3 hold real dates, you can leave Start and Finish
declared as Dates. I modified the code just a wee bit (changed Z variable to
Rw, to signify "row", and initialized it before the loop and move its
updating within the For..Next loop)... Rw is the row the dates are being
outputted to and now you can specify the starting row for the output by
changing the 1 in the Rw=1 statement to whatever starting row number you
want to start outputting your dates to. Here is the modified macro
incorporating the G2/G3 change as well as the Z/Rw change...

Sub PopulateWorkdays()
Dim X As Long, Rw As Long, Start As Date, Finish As Date, H() As String
H = Split(Join(WorksheetFunction.Transpose(Range("Holidays"))))
Start = Range("G2")
Finish = Range("G3")
Rw = 1
For X = Start To Finish
If Weekday(X, vbMonday) < 6 And UBound(Filter(H, X)) < 0 Then
Cells(Rw, "A").Value = X
Rw = Rw + 1
End If
Next
End Sub

 

[KevHardy]

Thanks Rick

No, assuming G2 and G3 hold real dates, you can leave Start and Finish
declared as Dates. I modified the code just a wee bit (changed Z variable to
Rw, to signify "row", and initialized it before the loop and move its
updating within the For..Next loop)... Rw is the row the dates are being
outputted to and now you can specify the starting row for the output by
changing the 1 in the Rw=1 statement to whatever starting row number you
want to start outputting your dates to. Here is the modified macro
incorporating the G2/G3 change as well as the Z/Rw change...

Sub PopulateWorkdays()
Dim X As Long, Rw As Long, Start As Date, Finish As Date, H() As String
H = Split(Join(WorksheetFunction.Transpose(Range("Holidays"))))
Start = Range("G2")
Finish = Range("G3")
Rw = 1
For X = Start To Finish
If Weekday(X, vbMonday) < 6 And UBound(Filter(H, X)) < 0 Then
Cells(Rw, "A").Value = X
Rw = Rw + 1
End If
Next
End Sub

--
Rick (MVP - Excel)




.