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
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
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
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
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
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!
Thanks for your help..... It works!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks