Convert length of time to years, months, and days

[meghannclaire]

I have a years of service calculation that I need broken out into the number of years, months, and days. For example:

Hire Date: 1/1/2007
Todays Date: 7/24/2008
Length of Service: 1 year, 7 months, 24 days

Can anyone help me create a formula that can give me the length of service as it appears above? I think it will be a combination of datediff and concatenating, but I'm just not able to wrap my brain around it to find the solution.

Any help would be appreciated, thanks!

 

[raskew]

Hi -

Don't have anything that comes up with that result, but you could try:

? fageYMD(#1/1/07#, #7/24/08#)
1 year 6 months 23 days

Copy/paste this to a standard module, then call as shown:

Function fAgeYMD(StartDate As Date, EndDate As Date) As String
'Purpose:   Returns the difference between StartDate and EndDate in full years, months and days
'Coded by:  raskew
'To call:
' ? fAgeYMD(#7/6/54#, #10/3/84#)
'Returns:
' 30 years 2 months 28 days

Dim inthold As Integer
Dim dayHold As Integer

   inthold = Int(DateDiff("m", StartDate, EndDate)) + _
             (EndDate < DateSerial(year(EndDate), month(EndDate), Day(StartDate)))

   If Day(EndDate) < Day(StartDate) Then
      dayHold = DateDiff("d", StartDate, DateSerial(year(StartDate), month(StartDate) + 1, 0)) + Day(EndDate)
   Else
      dayHold = Day(EndDate) - Day(StartDate)
   End If
   
   fAgeYMD = Int(inthold / 12) & " year" & IIf(Int(inthold / 12) <> 1, "s ", " ") _
             & inthold Mod 12 & " month" & IIf(inthold Mod 12 <> 1, "s ", " ") _
             & LTrim(str(dayHold)) & " day" & IIf(dayHold <> 1, "s", "")

End Function

HTH - Bob

 

[The_Doc_Man]

You should note that the answer to that question depends on something that must be defined first.

If your starting date is in the range [Jan 1 - Jun 30] and the ending date is in the second half of the year - and it isn't a leap year - then the number of days and months gets trickier to manage.

If you play with the Datepart functions, they will try to compensate, but the result is not guaranteed to be consistent depending on how you use it. The answer computed that way might be off one day for every leap year contained in the period of consideration.

Not sure I have a ready solution, but just be aware that sometimes you need to be careful in what you ask because it contains an ambiguity. Elapsed time is one of those cases. MS Access does its best, but we humans screwed up time flow with our irregular calendars long before anyone used computers to do time computations.

 

[sbenj69]

Raskew's code was essentially correct, I modified it to compensate for Leap Years

Option Compare Database
Function fAgeYMD(StartDate As Date, EndDate As Date) As String
'Purpose:   Returns the difference between StartDate and EndDate in full years, months and days
'Coded by:  raskew
'To call:
' ? fAgeYMD(#7/6/54#, #10/3/84#)
'Returns:
' 30 years 2 months 28 days
Dim inthold As Integer
Dim dayHold As Integer
Dim LeapStart As Integer
Dim LeapEnd As Integer
Dim LeapDays As Integer
   If StartDate > DateSerial(Year(StartDate), 2, 29) Then
      LeapStart = Int(DatePart("yyyy", StartDate) / 4) + 1
   Else
      LeapStart = Int(DatePart("yyyy", StartDate) / 4)
   End If
   
   If EndDate > DateSerial(Year(EndDate), 2, 29) Then
      LeapEnd = Int(DatePart("yyyy", EndDate) / 4) + 1
   Else
      LeapEnd = Int(DatePart("yyyy", EndDate) / 4)
   End If
   LeapDays = LeapEnd - LeapStart
   
   inthold = Int(DateDiff("m", StartDate, EndDate)) + _
             (EndDate < DateSerial(Year(EndDate), Month(EndDate), Day(StartDate)))
   If Day(EndDate) < Day(StartDate) Then
      dayHold = DateDiff("d", StartDate, DateSerial(Year(StartDate), Month(StartDate) + 1, 0)) + Day(EndDate)
   Else
      dayHold = Day(EndDate) - Day(StartDate)
   End If
   
   fAgeYMD = Int(inthold / 12) & " year" & IIf(Int(inthold / 12) <> 1, "s ", " ") _
             & inthold Mod 12 & " month" & IIf(inthold Mod 12 <> 1, "s ", " ") _
             & LTrim(Str(dayHold)) & " day" & IIf(dayHold <> 1, "s", "") _
             & " And " & LeapDays & " Leap-days observed"
End Function

 

[raskew]

Hi -

You've lost me here. Would you provide an example where the original code provides an incorrect return, which is then corrected by the modified code.

This bit of code correctly compensates for leap years. Note that it's only an issue if the StartDate is in February.

*******************************
If day(EndDate) < day(StartDate) Then
dayHold = DateDiff("d", StartDate, DateSerial(year(StartDate), month(StartDate) + 1, 0)) + day(EndDate)
Else
dayHold = day(EndDate) - day(StartDate)
End If
*******************************
month(StartDate) + 1, 0 returns the last day of the start month, whether it's 28, 29, 30 or 31 days. Example:

Non leap year:
startdate = #2/15/07#
Enddate = #3/14/07#
? DateDiff("d", StartDate, DateSerial(year(StartDate), month(StartDate) + 1, 0)) + Day(EndDate)
27

Leap Year:
startdate = #2/15/08#
Enddate = #3/14/08#
? DateDiff("d", StartDate, DateSerial(year(StartDate), month(StartDate) + 1, 0)) + Day(EndDate)
28
*******************************

I've yet to come up with a combination where the modified code returns a response different from the original code.

Thanks - Bob

 

[sbenj69]

Edit: Never mind.... I see that your code works regardless of leap years...... However, it doesn't show how many leap days were involved, not that it matters in this instance.

Heh, that's what I get for reading and not testing the code

 

[raskew]

Hey -

I hear you. Had I paid closer attention, would have realized that it's the same code, with the blurb about leap years added.

It might be an interesting drill to come up with a formula that shows how many leap years impact the start and end dates.

If StartDate > DateSerial(Year(StartDate), 2, 29) Then... doesn't do it, since dateserial(year(startdate), 2,29)) will just translate to 3/1/XX if it's a non-leap year.

Best wishes - Bob

 

[sbenj69]

guess you could do:

If StartDate >= DateSerial(Year(StartDate), 3, 1) Then...