DATEDIF(); an alternative available?

[cate]

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/micr...bedf5f2985d/f15b5e7e4a17c106#f15b5e7e4a17c106

 

[Mike H]

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

 

[Rick Rothstein]

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

 

[Rick Rothstein]

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

 

[cate]

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

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

 

[Mike H]

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