• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

vba script for date difference between two columns

abhi_jain80

New Member
Hi folks,

I need to calculate the date difference between the two columns A and B and store the value in C column. There are around 100k rows and some missing dates in the B column. Here is the vba code I am using...it is absolutely working fine what I need but taking around 15 mins to run on my machine. Just thinking, if there is any better optimized way to do the same? Thanks in advance...

>>> use code - tags <<<
Code:
Sub DateDifference()

Dim FD As Date
Dim LD As Date
Dim LR As Long

LR = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LR

FD = Range("A" & i).Value
LD = Range("B" & i).Value

If Range("B" & i).Value = "" Then
Range("C" & i).Value = ""
Else
If DateDiff("d", FD, LD) <= 0 Then
Range("C" & i).Value = 1
Else
Range("C" & i).Value = DateDiff("d", FD, LD)

End If
End If
Next i

End Sub
 
Last edited by a moderator:

Marc L

Excel Ninja
Hi,​
according to any Excel forum rules you forgot the link(s) of other forum(s) where you already have created the same thread !
And use the 3 dots icon when posting any code here …​
 

Marc L

Excel Ninja
Ok, thanks.​
Did you try at least a formula which is often faster than any bad looping cell-by-cell code ?​
 
Top