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

Hi,

Datedif was part of the ATP in E2003 butis now built in to E2007 but remains
undocumented.

have a look here

http://www.cpearson.com/excel/datedif.aspx

Mike



"cate" wrote:

> 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
> .
>

You should read my message at the link that the OP included in his message.

--
Rick (MVP - Excel)


"Mike H" <(E-Mail Removed)> wrote in message
news:3007A400-C713-488B-865B-(E-Mail Removed)...
> Hi,
>
> Datedif was part of the ATP in E2003 butis now built in to E2007 but
> remains
> undocumented.
>
> have a look here
>
> http://www.cpearson.com/excel/datedif.aspx
>
> Mike
>
>
>
> "cate" wrote:
>
>> 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
>> .
>>

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" <(E-Mail Removed)> wrote in message
news:c97d0302-3ada-4f00-8d2c-(E-Mail Removed)...
>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

On Dec 18, 9:19*am, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> 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" <catebekens...@yahoo.com> wrote in message
>
> news:c97d0302-3ada-4f00-8d2c-(E-Mail Removed)...
>
> >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...rogramming/bro...

I will give it a shot. Thank you very much.

Rick,

It seems to me this is a mess of Microsoft making, the function should have
been removed from E2007 or supported. The only error I was aware of is where
it can return -1 days if doing (say) as DOB calculation using years, months
and days, This latest bug is new to me and as a result I'll stop using or
recommending it. Thanks for the tip.

Mike

"Rick Rothstein" wrote:

> You should read my message at the link that the OP included in his message.
>
> --
> Rick (MVP - Excel)
>
>
> "Mike H" <(E-Mail Removed)> wrote in message
> news:3007A400-C713-488B-865B-(E-Mail Removed)...
> > Hi,
> >
> > Datedif was part of the ATP in E2003 butis now built in to E2007 but
> > remains
> > undocumented.
> >
> > have a look here
> >
> > http://www.cpearson.com/excel/datedif.aspx
> >
> > Mike
> >
> >
> >
> > "cate" wrote:
> >
> >> 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
> >> .
> >>
>
> .
>