 What was the problem:

In finance-accounting area some companies consider workdays as below:

• WD-5: the last fifth working day from the previous month
• WD-1: the last working day from the previos month
• WD1: the first working day from the month
• WD2: the second working day from the month

How can we create an algorithm that will automatically generate 2 columns like in the bellow image for all months from a given year, considering that the weekends and some given legal holiday dates should be excluded ? After I thought a little bit, the steps to my solution are the following:

• First we need to calculate the total number of days for each month.
• Then we need to check each day from each month if it is in weekend or in the list with legal holidays.
• Save in “a temporary location from memory” (ex: an array) a list with all the days that are work days and are not in the list with holidays.
• Iterate in the list with correct work days from each month and calculate the WDX/WD-X as in the specifications.
• For this, my algorithm is: if the total number of workdays from the month minus the no of current checked day is < 5 then -X (from WD-X) is the no of current checked day minus the total number of workdays minus 1. If the total number of workdays from the month minus the current checked day is > 5 then the X (from WDX) will be the no of current checked day (please see in the code below !)
• To implement this algorithm I used Excel VBA and a 2 columns variant array that stores the desired data and then writes it to the Excel Spreadsheet. To better understand how arrays work in Excel VBA please read this article.

The final VBA code that I wrote to solve the problem is the following:

```Sub create_workdays()

'declare and fill in holidays array
'If you add more legal holidays ensure that you modify the length of array when declared:
Dim holidays(1) As Long

holidays(0) = CLng(#1/1/2016#)
holidays(1) = CLng(#12/25/2016#)

'change year if you need to calculate days for another year
CYear = 2016
CDay = 1

'array with the working days
Dim arr() As Variant

Erase arr()

Set sht = ActiveSheet

lr = sht.Cells(sht.Rows.Count, "E").End(xlUp).Row + 1

'calculate working days from each months
For i = 1 To 12

'calculate the no of days from each month
no_days = DaysInMonth(CYear, i)

counter = 0

validRow = 0

'check if each day is a working day or weekend.
For j = 1 To no_days

current_date = DateSerial(CYear, i, j)

'If it is a working day and is not in the array with holidays,
'write it in the array with working days.
Select Case Weekday(current_date, vbMonday)

Case 1 To 5

'search the current day in the holidays array
pos = Application.Match(CLng(current_date), holidays, 0)

If IsError(pos) Then

'redim the array and write the valid day to the first column of the workdays array
validRow = validRow + 1
ReDim Preserve arr(1 To 2, 1 To validRow)
arr(1, validRow) = CLng(current_date)

counter = counter + 1

End If

End Select

Next j

'calculate the workdays (WD X) for the i month in the second column of the array.
'elements variable represents the total workdays from that month without holidays
elements = UBound(arr, 2)
validRow = 0

For k = 1 To elements

validRow = validRow + 1

'if the total no of days from array minus the current iterated days is < 5 that means
'that we need to write a WD-X where X is k minus elements -1
If (elements - k) < 5 Then

arr(2, validRow) = "WD " & (k - elements - 1)

'normal WD X
Else:
arr(2, validRow) = "WD " & validRow

End If
Next k

'write the array with workdays dates and WDX/WD-5 in columns A and B of the Activesheet
sht.Range("A" & lr & ":B" & lr + counter - 1) = WorksheetFunction.Transpose(arr)
lr = lr + counter

Erase arr

'go to the next month and do the same calculations
Next i

End Sub

Public Function DaysInMonth(ByVal myYear As Long, ByVal myMonth As Long) As Long
DaysInMonth = Day(DateSerial(myYear, myMonth + 1, 1) - 1)
End Function```

You can see below a sample of the data generated by this algorithm for 2016 (the third column is presented only for exemplification):

DateWDX/WD-XWeekday
04/01/2016WD1Monday
05/01/2016WD2Tuesday
28/01/2016WD-2Thursday
29/01/2016WD-1Friday
01/02/2016WD1Monday
02/02/2016WD2Tuesday
28/04/2016WD-2Thursday
29/04/2016WD-1Friday
02/05/2016WD1Tuesday
03/05/2016WD1Wednesday

Then you can import this data into you Outlook Calendar. To do so, save as a CSV file the sheet with the data generated by this algorithm and then follow the steps from this article to import the file.

After you do this you will see in the Outlook Calendar in each workday the events with subject “WDX”/”WD-X” as in the below example:  