Datediff in vba

Printable View

05-19-2008, 04:09 AM
sujittalukde

Datediff in vba

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

Code:

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)
05-19-2008, 04:21 AM
Bob Phillips

It gives me 0.
05-19-2008, 04:35 AM
sujittalukde

At this line I have substracted 1

Code:

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
05-19-2008, 04:43 AM
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]
05-19-2008, 09:43 PM
sujittalukde

Thanks xld, its working correct.