DateDiff() problem

[Brad]

Excel 2007 (with compatibility to 2003

Sub CalcAge()
Range("IssAgeP").Value = DateDiff("yyyy", Range("C7").Value,
Range("c5").Value)
End Sub

C7 = 8/4/1962
C5 = 2/4/2009

Expect to get 46, getting 47 - Is the problem with using Range().value?

 

[Rick Rothstein]

It looks like DateDiff is simply doing this...

Year(EndDate) - Year(StartDate

You can see that by first trying these dates...

EndDate = #1/31/2009#
StartDate = #12/1/1962#

and then these...

EndDate = #12/31/2009#
StartDate = #1/1/1962#

How did you want to calculate the difference... round down to the nearest
full year or round up or down depending on if the excess is a more or less
than a half year?

 

[Mike H]

Brad,

datediff can be a pain as a worksheet function or in VB so in Vb I would
generally use this

Range("IssAgeP").Value = Int(CStr((Range("c5").Value - Range("c7").Value) /
365.25))


But if you want to persist with datediff then this seems to work

Range("IssAgeP").Value = Int(DateDiff("d", Range("C7").Value,
Range("c5").Value) / 365.25)

Mike

 

[ryguy7272]

This should do it for you!

Sub CalcAge()
Range("IssAgeP") = "The months between " & Range("C5") & " and " &
Range("C7") & " is " & DateDiff("m", Range("C7"), Range("C5")) & " months!"
'Assume IssAgeP is a NamedRnage for Cell E5
Range("E6") = (DateDiff("m", Range("C7"), Range("C5")) / 12) & " years!"
End Sub


HTH,
Ryan---

 

[Mike H]

forget the cstr

Range("h1").Value = Int(((Range("c5").Value - Range("c7").Value) / 365.25))

 

[Brad]

I was wanted it do work in a similar fashion as the function datedif(), I
modified the code so that it works - Thanks for the input

 

[Brad]

I agree that datediff could work better - I modified the code by looking at
the month and day as well as the year to get what was needed

Thanks!