Back on February 27, 2012, I published my "UDF (user defined function) replacement for Excel's DATEDIF function" article which provide a replacement function for the flawed, undocumented DATEDIF function (see my "Recommendation: Do not use the undocumented DATEDIF function" 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...
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".Code: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
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.
Reply With Quote
Originally Posted by snb
When I quickly scanned the output list, the 4-day gap caught my eye and I didn't pay attention to the February/March switch-over. I am going to blame my confusion on not enough sleep and nowhere near enough coffee to compensate.
Thanks for following up with this. Much appreciated.
Bookmarks