+ Reply to Thread
Results 1 to 6 of 6

Calculating the difference between dates

  1. #1
    Registered User
    Join Date
    04-13-2005
    Posts
    3

    Calculating the difference between dates

    Hello,

    I attached a file Test.zip that represents the format I receive open and close details on accounts.

    I need to be able to calculate how "old" the account was. Year, Month, Day.

    Can anyone help?

    Pieter
    Attached Files Attached Files

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996
    You can convert any of those values into dates using a formula like this:
    =DATE(LEFT(B2,4),MID(B2,5,2),RIGHT(B2,2))

    Once that is done, determining the age becomes simple math. (NewDate-OldDate)

    I hope that helps.

    Regards,
    Ron

  3. #3
    Registered User
    Join Date
    04-13-2005
    Posts
    3
    Thanks....

    I am, however, a novice when it comes to Excel. Is it possible for you to use the attached file and provide a working example?

    Pieter

  4. #4
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996
    OK...Here's one way to display what I think you want:

    Using your TEST.XLS Example:
    C2: =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
    D2: =DATE(LEFT(B2,4),MID(B2,5,2),RIGHT(B2,2))
    E2: =INT((+D2-C2)/365.25)&" Yrs "&(MOD((+D2-C2)/365.25,1)*365.25)&" Days"

    Using your data from Row 2 these should be your results:
    C2: 02/23/1984
    D2: 08/19/2004
    E2: 20 Yrs 178 Days

    I hope that points you in the right direction.

    Regards,
    Ron

  5. #5
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Here's another way...

    Using the first two formulas provided by Ron, enter the following formula in C2 and copy down:

    =DATEDIF(C2,D2,"Y")&" Years, "&DATEDIF(C2,D2,"YM")&" Months, "&DATEDIF(C2,D2,"MD")&" Days"

    You'll need to enable the Analysis ToolPak...

    Tools > Add-Ins > and check Analysis ToolPak

    Hope this helps!

  6. #6
    Registered User
    Join Date
    04-13-2005
    Posts
    3
    Thanks for your help..... It works!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1