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
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    14

    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.

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    15
    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.

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
  •