Hi,

Is there a way to put Gord formula into a VBA function and say name it
YrsMthsDays whereby the formula would be =YrsMthsDays(A1,A2)

Thanks, Rob

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Mike

Your result would be 9 years and 23 days which Excel rounds to 9 years and
1
month.

What would you like to have displayed?

9 years and 23/31 months?

This formula will give you 9 years, 0 months, 23 days

=DATEDIF(A1,A2,"y") &" years, "& DATEDIF(A1,A2,"ym")&" months, "&
DATEDIF(A1,A2,"md") & " days"

Where A1 holds earliest date.

Gord Dibben Excel MVP

On Sat, 27 Nov 2004 10:19:02 -0800, "Mike New" <Mike
wrote:

I am trying to calculate chronological age - years and complete months -
for
school report purposes. I can get Years and months but not accurately -
say
for a date of birth 22nd November 1995 and a date of the 15th December - I
get 9 years and 1 month.

It would actually quicker to keep the formula in the worksheet. And if you keep
the formula handy (in a text file or sample workbook or just a link to Chip's
site), it wouldn't be too hard to copy it into your worksheet.

But if you want...

Option Explicit
Function YrsMthsDays(rng1 As Range, rng2 As Range) As Variant

Dim iCtr As Long
Dim myVals(0 To 2) As Long
Dim myIntervals As Variant
Dim myStrs As Variant
Dim myOutput As String

myIntervals = Array("y", "ym", "md")
myStrs = Array(" years, ", " months, ", " days")

myOutput = ""
For iCtr = LBound(myVals) To UBound(myVals)
myVals(iCtr) = Application.Evaluate("datedif(" _
& rng1.Address(external:=True) _
& "," & rng2.Address(external:=True) _
& ",""" & myIntervals(iCtr) & """)")
myOutput = myOutput & myVals(iCtr) & myStrs(iCtr)
Next iCtr

YrsMthsDays = myOutput

End Function

Rob wrote:

Hi,

Is there a way to put Gord formula into a VBA function and say name it
YrsMthsDays whereby the formula would be =YrsMthsDays(A1,A2)

Thanks, Rob

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Mike

Your result would be 9 years and 23 days which Excel rounds to 9 years and
1
month.

What would you like to have displayed?

9 years and 23/31 months?

This formula will give you 9 years, 0 months, 23 days

=DATEDIF(A1,A2,"y") &" years, "& DATEDIF(A1,A2,"ym")&" months, "&
DATEDIF(A1,A2,"md") & " days"

Where A1 holds earliest date.

Gord Dibben Excel MVP

On Sat, 27 Nov 2004 10:19:02 -0800, "Mike New" <Mike
wrote:

I am trying to calculate chronological age - years and complete months -
for
school report purposes. I can get Years and months but not accurately -
say
for a date of birth 22nd November 1995 and a date of the 15th December - I
get 9 years and 1 month.


--

Dave Peterson

Thanks Dave, just what I needed, helps with my learning cycle! Rob

"Dave Peterson" wrote in message
...
It would actually quicker to keep the formula in the worksheet. And if
you keep
the formula handy (in a text file or sample workbook or just a link to
Chip's
site), it wouldn't be too hard to copy it into your worksheet.

But if you want...

Option Explicit
Function YrsMthsDays(rng1 As Range, rng2 As Range) As Variant

Dim iCtr As Long
Dim myVals(0 To 2) As Long
Dim myIntervals As Variant
Dim myStrs As Variant
Dim myOutput As String

myIntervals = Array("y", "ym", "md")
myStrs = Array(" years, ", " months, ", " days")

myOutput = ""
For iCtr = LBound(myVals) To UBound(myVals)
myVals(iCtr) = Application.Evaluate("datedif(" _
& rng1.Address(external:=True) _
& "," & rng2.Address(external:=True) _
& ",""" & myIntervals(iCtr) & """)")
myOutput = myOutput & myVals(iCtr) & myStrs(iCtr)
Next iCtr

YrsMthsDays = myOutput

End Function

Rob wrote:

Hi,

Is there a way to put Gord formula into a VBA function and say name it
YrsMthsDays whereby the formula would be =YrsMthsDays(A1,A2)

Thanks, Rob

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Mike

Your result would be 9 years and 23 days which Excel rounds to 9 years
and
1
month.

What would you like to have displayed?

9 years and 23/31 months?

This formula will give you 9 years, 0 months, 23 days

=DATEDIF(A1,A2,"y") &" years, "& DATEDIF(A1,A2,"ym")&" months, "&
DATEDIF(A1,A2,"md") & " days"

Where A1 holds earliest date.

Gord Dibben Excel MVP

On Sat, 27 Nov 2004 10:19:02 -0800, "Mike New" <Mike
wrote:

I am trying to calculate chronological age - years and complete
months -
for
school report purposes. I can get Years and months but not
accurately -
say
for a date of birth 22nd November 1995 and a date of the 15th
December - I
get 9 years and 1 month.


--

Dave Peterson