Consulting

Results 1 to 7 of 7

Thread: DATE DIFF function in years

  1. #1
    VBAX Regular
    Joined
    Mar 2019
    Posts
    9
    Location

    DATE DIFF function in years

    Hello,
    I am trying to use the date diff function to get the number of years between two dates as a decimal.
    For example, I want the datediff function to return 16.353 when I get the difference between 12/31/2018 and 8/23/2002.
    My date1 date, 12/31/2018, is saved as a string and returns "12/31/2018".
    My date2 date, 8/23/2002, is saved as a string also but is saved as "20020823".
    I have tried various formatting changed but I constantly get overflow and type mismatch errors.

    I am fairly new to VBA so any help or tips would be greatly appreciated.

    Thanks!

  2. #2
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    Hello mh2020!
    Can you tell me how 16.353 was calculated?

  3. #3
    VBAX Mentor
    Joined
    Apr 2009
    Location
    Kingsbury
    Posts
    423
    Location
    without see your workbook (16.353)????
    try this i don't use date diff function
    also format your columns to use dates
    Private Sub CalDatediff()
        With Worksheets("Sheet1")
            .Range("E2").Value = CLng(CDate(.Range("A2"))) - CLng(CDate(.Range("B2")))
        End With
    End Sub

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Sub M_snb()
       MsgBox Int(DateDiff("m", "8/23/2002", "12/31/2018") / 12) + (DatePart("y", "12/31/2002") - DatePart("y", "8/23/2002")) / 365
    End Sub

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    What about Leap Years ??
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Quote Originally Posted by Paul_Hossler View Post
    What about Leap Years ??
    Rather evident:

    Sub M_snb()
      MsgBox Int(DateDiff("m", "8/23/2002", "12/31/2018") / 12) + (DatePart("y", "12/31/2002") - DatePart("y", "8/23/2002")) / DatePart("y", "12/31/2008")
    End Sub

  7. #7
    VBAX Regular
    Joined
    Mar 2019
    Posts
    9
    Location
    Thanks for the help everyone.

    16.353 was calculated as the number of years in decimals between the two dates, 16.353 years between 08/23/2002 and 12/31/2018.

    I used a formula similar to the one that was posted above.
    However since my code was used on multiple lines of data I just saved the dates as strings and then used those values instead.

    I will mark this as solved and have my code below in case anyone else runs into a similar problem.
    Thanks!

    TimeElapsed = ((ValYear - IssueYear) + ((ValMonth - IssueMonth) / 12) + ((ValDay - IssueDay) / 365))

Posting Permissions

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