Datediff in vba

[sujittalukde]

I am using the following code in vba to find the difference between years :

For j = 2 To 20
If Range("H" & j).Value = "" Or Range("I" & j).Value = "" Then
    Range("J" & j).Value = ""
Else
    Range("J" & j).Value = (DateDiff("yyyy", DateValue((Range("H" & j).Value)), DateValue((Range("i" & j).Value))) - 1)
    'ActiveSheet.Range("J" & j).Value = "=IF(OR(H2="",I2=""),"",DATEDIF(H2,I2,""y""))"
End If
Next j

Suppose date in H2 = 06/08/2007 and date in I2 = 30/04/2008 Here worksheet datedif formula shows ?0? (Zero) years but VBA DATEDIFF is showing 1 year which is wrong.

How can I use datediff function in VBA to get correct result? (Either of VBA DATEDIFF or Worksheet function DATEDIF, anyone is accepteable)

[Bob Phillips]

It gives me 0.

[sujittalukde]

At this line I have substracted 1

Range("J" & j).Value = (DateDiff("yyyy", DateValue((Range("H" & j).Value)), DateValue((Range("i" & j).Value))) - 1)

If you remove the -1 then wrong result will come.

If you consider -1 then wrong result will come in this case
Date in H2 :01/04/2008
Date in I2 : 30/04/2011
Worksheet Datedif gives 3 VBA Datediff will give 2

[Bob Phillips]

Oh yeah I see, It seems to take different years as a years difference.

Try this

[vba]

Dim j
For j = 2 To 20
If Range("H" & j).Value = "" Or Range("I" & j).Value = "" Then
Range("J" & j).Value = ""
Else
Range("J" & j).Value = Application.Evaluate("DateDif(" & Range("H" & j).Address & "," & Range("i" & j).Address & ", ""Y"")")
'ActiveSheet.Range("J" & j).Value = "=IF(OR(H2="",I2=""),"",DATEDIF(H2,I2,""y""))"
End If
Next j
[/vba]

[sujittalukde]

Thanks xld, its working correct.