PDA

View Full Version : Datediff in vba



sujittalukde
05-19-2008, 04:09 AM
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
05-19-2008, 04:21 AM
It gives me 0.

sujittalukde
05-19-2008, 04:35 AM
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
05-19-2008, 04:43 AM
Oh yeah I see, It seems to take different years as a years difference.

Try this



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

sujittalukde
05-19-2008, 09:43 PM
Thanks xld, its working correct.