Rick Rothstein
05-12-2012, 11:43 PM
Back on February 27, 2012, I published my "UDF (user defined function) replacement for Excel's DATEDIF function (http://www.excelfox.com/forum/f22/udf-user-defined-function-replacement-excels-datedif-function-322/)" article which provide a replacement function for the flawed, undocumented DATEDIF function (see my "Recommendation: Do not use the undocumented DATEDIF function (http://www.excelfox.com/forum/f22/recommendation-do-not-use-undocumented-datedif-function-321/)" article for why I consider it flawed). That function, like the one it replaces, provides date part answers one at a time (month, days remaining, etc.). While this is useful, in a lot of cases when one is determining the span of time between two dates, they would like a simple answer like "62 years, 5 months, 17 days" without having to construct it piecemeal. The function below does this...
Function YMD(ByVal StartDate As Date, _
Optional ByVal EndDate As Variant, _
Optional LeapDayInNonLeapYearIsMar1 As Boolean = True) As String
Dim TempDate As Date, NumOfHMS As Double
Dim NumOfYears As Long, NumOfMonths As Long, NumOfWeeks As Long, NumOfDays As Long
StartDate = Int(StartDate)
If IsMissing(EndDate) Then
Application.Volatile
EndDate = Date
Else
EndDate = Int(EndDate)
End If
If Not LeapDayInNonLeapYearIsMar1 And IsDate("2/29/" & Year(StartDate)) Then
If Format(StartDate, "m/d") = "2/29" Then StartDate = StartDate - 1
End If
NumOfYears = DateDiff("yyyy", StartDate, EndDate)
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
This function, which can be called from other VB code or as a UDF (user defined function) from within a worksheet formula directly in a cell, outputs the time span as a text string (in the above indicated format) with the date part numbers labeled and with those labels properly pluralized; that is, if there is only one of the date parts, the "s" is left off of its label... an example output being, "2 years, 1 month, 4 days" or, in the extreme case, "1 year, 1 month, 1 day".
Note that there are three arguments to this function, one required and two optional. The first argument, an actual date (not a text string) is required and is the starting date for the calculation. The second argument, also an actual date (if provided) is optional and is the ending date... if omitted, the current date will be used for the calculation and the function will be Volatile (meaning the date difference will update with each passing day whenever the sheet is recalculated). The last argument, also optional, handles the calculation if the start date is a leap day (February 29th) and the ending date is in a non-leap year... if True, the default setting, the February 29th start date will be assumed to be March 1st in the non-leap years; if False, then the February 29th start date will be assumed to be February 28th in the non-leap years. Most people adopt the March 1st rule, but there are a significant number of people who opt for the February 28th rule, so I included the third argument to accomodate them.
Function YMD(ByVal StartDate As Date, _
Optional ByVal EndDate As Variant, _
Optional LeapDayInNonLeapYearIsMar1 As Boolean = True) As String
Dim TempDate As Date, NumOfHMS As Double
Dim NumOfYears As Long, NumOfMonths As Long, NumOfWeeks As Long, NumOfDays As Long
StartDate = Int(StartDate)
If IsMissing(EndDate) Then
Application.Volatile
EndDate = Date
Else
EndDate = Int(EndDate)
End If
If Not LeapDayInNonLeapYearIsMar1 And IsDate("2/29/" & Year(StartDate)) Then
If Format(StartDate, "m/d") = "2/29" Then StartDate = StartDate - 1
End If
NumOfYears = DateDiff("yyyy", StartDate, EndDate)
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
This function, which can be called from other VB code or as a UDF (user defined function) from within a worksheet formula directly in a cell, outputs the time span as a text string (in the above indicated format) with the date part numbers labeled and with those labels properly pluralized; that is, if there is only one of the date parts, the "s" is left off of its label... an example output being, "2 years, 1 month, 4 days" or, in the extreme case, "1 year, 1 month, 1 day".
Note that there are three arguments to this function, one required and two optional. The first argument, an actual date (not a text string) is required and is the starting date for the calculation. The second argument, also an actual date (if provided) is optional and is the ending date... if omitted, the current date will be used for the calculation and the function will be Volatile (meaning the date difference will update with each passing day whenever the sheet is recalculated). The last argument, also optional, handles the calculation if the start date is a leap day (February 29th) and the ending date is in a non-leap year... if True, the default setting, the February 29th start date will be assumed to be March 1st in the non-leap years; if False, then the February 29th start date will be assumed to be February 28th in the non-leap years. Most people adopt the March 1st rule, but there are a significant number of people who opt for the February 28th rule, so I included the third argument to accomodate them.