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
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
mydate2 = DateAdd(“d”, count, mydate2)
Cells(i, 2) = mydate2
mydate2 = WeekStart(IsoWeekNumber(Cells(i, 2)), Year(Cells(i, 2)))
Cells(i, 3) = mydate2

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

Published by

Dinesh Kumar Takyar

Welcome to! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: For a structured Excel VBA training course online you can visit:

One thought on “Automate Dates Calculations with Excel VBA”

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.