You might be able to use this function I wrote back in my compiled VB
days... it will should work in Excel as either a normal function to be
called by other VB code or as a UDF (User Defined Function) directly on the
worksheet...

Function YMD(StartDate As Date, EndDate As Date) As String
Dim TempDate As Date
Dim NumOfYears As Long
Dim NumOfMonths As Long
Dim NumOfWeeks As Long
Dim NumOfDays As Long
Dim NumOfHMS As Double
Dim TSerial1 As Double
Dim TSerial2 As Double
NumOfYears = DateDiff("yyyy", StartDate, EndDate)
TSerial1 = TimeSerial(Hour(StartDate), _
Minute(StartDate), Second(StartDate))
TSerial2 = TimeSerial(Hour(EndDate), _
Minute(EndDate), Second(EndDate))
NumOfHMS = 24 * (TSerial2 - TSerial1)
If NumOfHMS < 0 Then
NumOfHMS = NumOfHMS + 24
EndDate = DateAdd("d", -1, EndDate)
End If
StartDate = DateSerial(Year(EndDate), _
Month(StartDate), Day(StartDate))
If StartDate EndDate Then
StartDate = DateAdd("yyyy", -1, StartDate)
NumOfYears = NumOfYears - 1
End If
NumOfMonths = DateDiff("m", StartDate, EndDate)
StartDate = DateSerial(Year(EndDate), _
Month(EndDate), Day(StartDate))
If StartDate EndDate Then
StartDate = DateAdd("m", -1, StartDate)
NumOfMonths = NumOfMonths - 1
End If
NumOfDays = Abs(DateDiff("d", StartDate, EndDate))
YMD = CStr(NumOfYears) & " year" & _
IIf(NumOfYears = 1, "", "s")
YMD = YMD & ", "
YMD = YMD & CStr(NumOfMonths) & " month" & _
IIf(NumOfMonths = 1, "", "s")
YMD = YMD & ", "
YMD = YMD & CStr(NumOfDays) & " day" & _
IIf(NumOfDays = 1, "", "s")
End Function

--
Rick (MVP - Excel)


"cate" wrote in message
...
I wouldn't have a clue how to write a udf to replace this. In another
post, in this group, I was informed that this undocumented excel
function was broken in 2007 (in a service pak). Is an alternative
available?

Thank you.

http://groups.google.com/group/micro...5b5e7e4a17c106