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):
| Date | WDX/WD-X | Weekday |
|---|---|---|
| 04/01/2016 | WD1 | Monday |
| 05/01/2016 | WD2 | Tuesday |
| … | ||
| 28/01/2016 | WD-2 | Thursday |
| 29/01/2016 | WD-1 | Friday |
| 01/02/2016 | WD1 | Monday |
| 02/02/2016 | WD2 | Tuesday |
| … | ||
| 28/04/2016 | WD-2 | Thursday |
| 29/04/2016 | WD-1 | Friday |
| 02/05/2016 | WD1 | Tuesday |
| 03/05/2016 | WD1 | Wednesday |
| … | ||
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:
You can download sample files from here:
- Sample Excel Master file with VBA Code (before use delete the data from columns A and B. In case you don’t delete the code will write any new data starting from the last empty row)
- Sample CSV file saved from the Excel master file
If you have an idea of how to do this in an easier way please leave a comment.
