Find date difference using datediff function in VBA Excel – debugvba.com

datediff vba excel

Objective

To find the difference between 2 dates.

Approach

In this VBA Excel automation, we have the data in 2 columns (i.e. column A and Column B) in sheet1. Both the columns contain the sample date values. Here we aim to find the difference between the dates of column A and column B. We can find the difference in days, years, months etc. In this post we shall only focus on finding the difference in days only. You can refer to link provided in reference section in this post to get more details.

Code

In the code below, we have used DateDiff function to find the difference between 2 dates. In the code below, first we have found the last populated row in sheet1. Then we have looped through each row. For each row we have found the difference in value in column A and column B, then we have stored the result in column C.

 

Sub DateDifference()

Dim FirstDate As Date
Dim LastDate As Date
Dim LastRow As Long

'Find last row
LastRow = Range("A" & Rows.Count).End(xlUp).Row

'Loop through each row
For i = 2 To LastRow

FirstDate = Range("A" & i).Value
LastDate = Range("B" & i).Value

'Store the date difference in column C
Range("C" & i).Value = DateDiff("d", FirstDate, LastDate)

Next i

End Sub

 

In the image below, on the left side we can see that column C is empty, while on the right side we have showcased how the data looked like after executing the VBA code. In column C we can see the difference in terms of days.

 

datediff vba excel

 

Reference

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/datediff-function

Post you may like

Delete blank rows from worksheet in VBA Excel – debugvba.com