Automate Dates Calculations with Excel VBA


An HR person does the following entry: In column A2 she enters the hire dta. Now she wishes to calculate 90 days in the future including weekends. She also wishes to add the holidays during this period and finally display the date in column B2. In column C2 she wishes to display the closest non-holiday Monday. In column D2 she would like to calculate 14 days from the date in C2 ending on Sunday. Finally in cell E2 she wants to calculate 14 days from the date in cell D2 ending on a Monday.

Our Excel VBA will be then based on the following ideas:

1. We enter the hire date
2. We add 90 days to the entered date
3. We then count the number of holidays between the two dates from a list of holidays in sheet2 and add the count of holidays to the above data
4. Next we check whether the calculated date is a holiday and add 7 days if it is till we find a non-holiday date
5. We then move to the start of the week, that is, Monday of this date
6. Then we add 14 days to our calculated date. Since we wish to have the date of a Sunday we subtract 1.
7. Next we add 14 days to get our required date. Since we wish to have a Monday we add 1.

View the Excel VBA training video below and then study the vba code step by step:


Watch this video on YouTube.

Download a sample file by clicking on the Excel icon:


Here is the complete VBA code:
Dim count As Long
Dim mydate As Date
Dim mydate2 As Date
Public Function YearStart(WhichYear As Integer) As Date

Dim WeekDay As Integer
Dim NewYear As Date

NewYear = DateSerial(WhichYear, 1, 1)
WeekDay = (NewYear – 2) Mod 7 ‘Generate weekday index where Monday = 0

If WeekDay < 4 Then
YearStart = NewYear – WeekDay
Else YearStart = NewYear – WeekDay + 7
End If
End Function
Public Function WeekStart(WhichWeek As Integer, WhichYear As Integer) As Date
‘YearStart
WeekStart = YearStart(WhichYear) + ((WhichWeek – 1) * 7)
End Function

Public Function IsoWeekNumber(d1 As Date) As Integer
‘ Attributed to Daniel Maher
Dim d2 As Long d2 = DateSerial(Year(d1 – WeekDay(d1 – 1) + 4), 1, 3)
IsoWeekNumber = Int((d1 – d2 + WeekDay(d2) + 5) / 7)
End Function

Sub getcount()
Dim i As Long
Dim lastrow As Long
lastrow = Sheets(“Sheet2”).Range(“A” & Rows.count).End(xlUp).Row
count = 0
For i = 2 To lastrow
If Sheet2.Cells(i, 2) >= mydate And Sheet2.Cells(i, 2) <= mydate2 Then
count = count + 1
End If
Next i
End Sub

Sub getmydate2()
Dim i As Long
Dim lastrow As Long

lastrow = Sheets(“Sheet2”).Range(“A” & Rows.count).End(xlUp).Row

For i = 2 To lastrow
If Sheet2.Cells(i, 2) = mydate2 And Sheet2.Cells(i, 3) = “holiday” Then
mydate = DateAdd(“d”, 7, mydate2)
End If
Next i

End Sub

Sub calculatedates()
Dim i As Long
Dim lastrow As Long
Dim weekstartdate As Date

lastrow = Sheets(“sheet1”).Range(“A” & Rows.count).End(xlUp).Row
For i = 2 To lastrow
mydate = Cells(i, 1)
mydate2 = DateAdd(“d”, 90, mydate)
Cells(i, 2) = mydate2
getcount
mydate2 = DateAdd(“d”, count, mydate2)
Cells(i, 2) = mydate2
mydate2 = WeekStart(IsoWeekNumber(Cells(i, 2)), Year(Cells(i, 2)))
Cells(i, 3) = mydate2
getmydate2

mydate2 = DateAdd(“d”, 13, mydate2)
Cells(i, 4) = mydate2
mydate2 = DateAdd(“d”, 15, mydate2)
Cells(i, 5) = mydate2
Next i

End Sub
Further reading:
VBA Procedures For Dates And Times
Week numbers in Excel

One thought on “Automate Dates Calculations with Excel VBA

Leave a Reply

Your email address will not be published. Required fields are marked *