Results 1 to 8 of 8

Thread: The Number of Years, Months and Days Between Two Dates

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13

    The Number of Years, Months and Days Between Two Dates

    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...

    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
    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.
    Last edited by Rick Rothstein; 06-08-2012 at 10:27 PM.

  2. #2
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13

    datediff different

    Hi Rick,

    Nice function you wrote.
    You challenged me to do so too.

    Wouldn't this suffice ?

    Code:
    Function snb(x0 as string, x1 as string)
        x2 = DateDiff("m", x0, x1) - IIf(Day(x0) > Day(x1), 1, 0)
        c01 = x2 \ 12
        c02 = x2 Mod 12
        c03 = Day(x1) - Day(x0) + IIf(Day(x1) >= Day(x0), 0, Day(CDate(x1) - Day(x1)))
        snb = c01 & " year" & IIf(c01 = 1, ", ", "s, ") & c02 & " month" & IIf(c02 = 1, ", ", "s, ") & c03 & " day" & IIf(c03 = 1, "", "s")
    End Function
    Code:
    sub tst()
      msgbox snb("02-04-2011","22-02-2012")
    end sub
    Last edited by snb; 06-08-2012 at 12:33 PM.

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by snb View Post
    Hi Rick,

    Nice function you wrote.
    You challenged me to do so too.

    Wouldn't this suffice ?

    Code:
    Function snb(x0 as string, x1 as string)
        x2 = DateDiff("m", x0, x1) - IIf(Day(x0) > Day(x1), 1, 0)
        c01 = x2 \ 12
        c02 = x2 Mod 12
        c03 = Day(x1) - Day(x0) + IIf(Day(x1) >= Day(x0), 0, Day(CDate(x1) - Day(x1)))
        snb = c01 & " year" & IIf(c01 = 1, ", ", "s, ") & c02 & " month" & IIf(c02 = 1, ", ", "s, ") & c03 & " day" & IIf(c03 = 1, "", "s")
    End Function
    I am glad you posted your message because I think it led me to a hidden flaw in the underlying algorithm I used for my function (and which your code duplicates). First though, there is a problem with your code as posted... it appears to never find a difference of zero days between any dates. See the line highlighed below in purple... you cannot go from 66 years, 3 months, 1 day to 66 years, 3 months, 31 days for a difference of one day in the start date when keeping the end date steady. Your code appears to do a similar jump at every zero-day difference.

    A B C D E
    55 snb's
    formula
    rick's formula
    56 2/25/1946 6/7/2012 66 years, 3 months, 13 days 66 years, 3 months, 13 days
    57 2/26/1946 6/7/2012 66 years, 3 months, 12 days 66 years, 3 months, 12 days
    58 2/27/1946 6/7/2012 66 years, 3 months, 11 days 66 years, 3 months, 11 days
    59 2/28/1946 6/7/2012 66 years, 3 months, 10 days 66 years, 3
    months, 10 days
    60 3/1/1946 6/7/2012 66 years, 3
    months, 6 days
    66 years, 3
    months, 6 days
    61 3/2/1946 6/7/2012 66 years, 3 months, 5 days 66 years, 3 months, 5 days
    62 3/3/1946 6/7/2012 66 years, 3 months, 4 days 66 years, 3 months, 4 days
    63 3/4/1946 6/7/2012 66 years, 3 months, 3 days 66 years, 3 months, 3 days
    64 3/5/1946 6/7/2012 66 years, 3 months, 2 days 66 years, 3 months, 2 days
    65 3/6/1946 6/7/2012 66 years, 3 months, 1 day 66 years, 3 months, 1 day
    66 3/7/1946 6/7/2012 66 years, 3
    months, 31 days
    X 66 years, 3
    months, 0 days
    67 3/8/1946 6/7/2012 66 years, 2 months, 30 days 66 years, 2 months, 30 days
    68 3/9/1946 6/7/2012 66 years, 2 months, 29 days 66 years, 2 months, 29 days
    69 3/10/1946 6/7/2012 66 years, 2 months, 28 days 66 years, 2 months, 28 days

    Now, for the flaw. Look at the two lines I highlighted in red. The end dates are the same and the start dates differ by one day, yet our code indates a four day difference... and I cannot find where those four days ever get "made up for" later in the chart which means, I think, that the error is cummulative in some way. I would appreciate your thought (actually, anyone's thoughts) on this. I'll be re-examining my code later on today or tommorow as time permits, but for now, I will put a caution note on my original message.
    Last edited by Rick Rothstein; 06-08-2012 at 09:02 PM.

  4. #4
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Hi Rick,

    I fear you used an earlier version of my code.
    I amended it to perform the zero days difference calculation.
    It's so slight you can easily overlook it.

    Code:
    Function snb(x0 as string, x1 as string)
        x2 = DateDiff("m", x0, x1) - IIf(Day(x0) > Day(x1), 1, 0)
        c01 = x2 \ 12
        c02 = x2 Mod 12
        c03 = Day(x1) - Day(x0) + IIf(Day(x1) >= Day(x0), 0, Day(CDate(x1) - Day(x1)))
        snb = c01 & " year" & IIf(c01 = 1, ", ", "s, ") & c02 & " month" & IIf(c02 = 1, ", ", "s, ") & c03 & " day" & IIf(c03 = 1, "", "s")
    End Function
    Last edited by snb; 06-08-2012 at 09:49 PM.

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by snb View Post
    I fear you used an earlier version of my code.
    I amended it to perform the zero days difference calculation.
    It's so slight you can easily overlook it.
    Yes, I have been looking at the output from our formulas since early yesterday morning, so the odds are I grabbed your code before you edited it. Any thoughts on the other problem I raised (which both our codes exhibit)?

  6. #6
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Yes, it is no problem, unless you assume that a 'month' is a fixed quantity. Since it isn't it will produce odd results at first glance only.

    From 28 feb to 28 may is 4 months, until the 7th of june is 29,30,31 may + 7 days in june: 10 days in total
    From 01 march to the first of june is also 4 months (but comprising more days than the 3 months from 28 feb to 28 may).
    If you count in days, the difference must be 1, if you count in months & days, the result will differ dependent of the period in the year you are counting with.

    That's the nice thing about dates: it's a mixed 365,25 -number system, 12-number system, alternating 28,29,30,31-number system.

    So both our methods produce the correct results.

    Maybe a slight modification to make it more elegant:

    Code:
    Function snb(x0 As String, x1 As String)
        x2 = DateDiff("m", x0, x1) + (Day(x1) < Day(x0))
        c01 = x2 \ 12
        c02 = x2 Mod 12
        c03 = Day(x1) - Day(x0) -(Day(x1) < Day(x0)) * Day(CDate(x1) - Day(x1))
        snb = c01 & " year" & IIf(c01 = 1, ", ", "s, ") & c02 & " month" & IIf(c02 = 1, ", ", "s, ") & c03 & " day" & IIf(c03 = 1, "", "s")
    End Function
    Last edited by snb; 07-07-2012 at 09:27 PM.

  7. #7
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by snb View Post
    Yes, it is no problem, unless you assume that a 'month' is a fixed quantity. Since it isn't it will produce odd results at first glance only.

    From 28 feb to 28 may is 4 months, until the 7th of june is 29,30,31 may + 7 days in june: 10 day in total
    From 01 march to the first of june is also 4 months (but comprising more days than the 3 months from 28 feb to 28 may).
    If you count in days, the difference must be 1, if you count in months & days, the result will differ dependent of the period in the year you are counting with.

    That's the nice thing about dates: it's a miixed 365,25 -number system, 12-number system, alternating 28,29,30,31-number system.

    So both our methods produce the correct results.
    Yes, but of course you are right. 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.
    Last edited by Rick Rothstein; 06-08-2012 at 10:34 PM.

  8. #8
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    A nice occurrence of a bug 'between the ears' instead of in the code ,,,,

Similar Threads

  1. Replies: 0
    Last Post: 09-17-2012, 02:05 PM
  2. How to Extracting dates and days between 2 dates.
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 9
    Last Post: 08-11-2012, 09:11 PM
  3. Excel Datedif - Days of February
    By Excelfun in forum Excel Help
    Replies: 6
    Last Post: 06-10-2012, 02:32 PM
  4. Visual Plotter basis given dates and activity
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 2
    Last Post: 03-07-2012, 02:37 PM
  5. Number of Days In A Month
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 05-14-2011, 08:00 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •