Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
DATEDIF(); an alternative available?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
DATEDIF(); an alternative available?
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
DATEDIF(); an alternative available?
You should read my message at the link that the OP included in his message.
-- Rick (MVP - Excel) "Mike H" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
DATEDIF(); an alternative available?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
DATEDIF(); an alternative available?
On Dec 18, 9:19*am, "Rick Rothstein"
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" 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...rogramming/bro... I will give it a shot. Thank you very much. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
DATEDIF(); an alternative available?
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" wrote in message ... 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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
datedif; what's up? | Excel Programming | |||
DateDIF | Excel Programming | |||
DateDif Average? Damn DateDif | Excel Worksheet Functions | |||
=DATEDIF(B14,B4,"m") | Excel Discussion (Misc queries) | |||
DATEDIF | Excel Programming |