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?
--
Wag more, bark less

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?

--
Rick (MVP - Excel)


"Brad" <(E-Mail Removed)> wrote in message
news1A9A243-DEAD-4086-B2C7-(E-Mail Removed)...
> 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?
> --
> Wag more, bark less

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



"Brad" wrote:

> 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?
> --
> Wag more, bark less

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---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Mike H" wrote:

> 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
>
>
>
> "Brad" wrote:
>
> > 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?
> > --
> > Wag more, bark less

forget the cstr

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



"Mike H" wrote:

> 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
>
>
>
> "Brad" wrote:
>
> > 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?
> > --
> > Wag more, bark less

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
--
Wag more, bark less


"Rick Rothstein" wrote:

> 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?
>
> --
> Rick (MVP - Excel)
>
>
> "Brad" <(E-Mail Removed)> wrote in message
> news1A9A243-DEAD-4086-B2C7-(E-Mail Removed)...
> > 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?
> > --
> > Wag more, bark less
>
>

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!
--
Wag more, bark less


"Mike H" wrote:

> 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
>
>
>
> "Brad" wrote:
>
> > 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?
> > --
> > Wag more, bark less