Good day,
This is a good opportunity to learn from the expert people and I hope that I can be expert like you.
Unfortunately, I have a bad beginning. Now, I am trying to create a formula in Column C, but I failed.
Could you please help me in this Formula?
In Cell A1: 32 Y - 0 M
In Cell B1: 7 Y - 7 M
In Cell C1: =A1-B1 (I am trying to calculate the difference between the two cells.
I thought it is easy to do it, but it seems that it is really hard.
Thank you for your support.
Put this in C1:
=LEFT(A1,SEARCH("y",A1)-1)-LEFT(B1,SEARCH("y",B1)-1)-(MID(A1,FIND("-",A1)+1,3)-MID(B1,FIND("-",B1)+1,3)<0)&" y - "&IF(MID(A1,FIND("-",A1)+1,3)-MID(B1,FIND("-",B1)+1,3)<0,12,0)+MID(A1,FIND("-",A1)+1,3)-MID(B1,FIND("-",B1)+1,3)&" m"
Hope this helps.
Pete
One sledgehammer solution
=INT(LEFT(A1,FIND(" ",A1)-1)-LEFT(B1,FIND(" ",B1)-1)+(MID(A1,FIND(" - ",A1)+3,2)-MID(B1,FIND(" - ",B1)+3,2))/12)&" Y - "&INT(MOD(LEFT(A1,FIND(" ",A1)-1)-LEFT(B1,FIND(" ",B1)-1)+(MID(A1,FIND(" - ",A1)+3,2)-MID(B1,FIND(" - ",B1)+3,2))/12,1)*12)&" M"
And hey, if you think truncation is overrated (hint: it isn't) you could try:
=TEXT(DATE(TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",10)),10)),MAX(1,TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",10)),FIND("-",SUBSTITUTE(A1," ",REPT(" ",10)))+10,10))),1)-DATE(TRIM(LEFT(SUBSTITUTE(B1," ",REPT(" ",10)),10)),MAX(1,TRIM(MID(SUBSTITUTE(B1," ",REPT(" ",10)),FIND("-",SUBSTITUTE(B1," ",REPT(" ",10)))+10,10))),1),"Y")&" Y "&TEXT(DATE(TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",10)),10)),MAX(1,TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",10)),FIND("-",SUBSTITUTE(A1," ",REPT(" ",10)))+10,10))),1)-DATE(TRIM(LEFT(SUBSTITUTE(B1," ",REPT(" ",10)),10)),MAX(1,TRIM(MID(SUBSTITUTE(B1," ",REPT(" ",10)),FIND("-",SUBSTITUTE(B1," ",REPT(" ",10)))+10,10))),1),"M")&" M"
I'm feeling a little inadequate.
Edit: This post is completely wrong
Last edited by daffodil11; 12-30-2013 at 07:55 PM. Reason: Feeling inadequate
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
The Formula you typed contains an error.
I think i made something wrong,
Who are you addressing that post to?
Pete
Try this version
=LEFT(A1,FIND("Y",A1)-1)-LEFT(B1,FIND("Y",B1)-1)-(MID(A1,FIND("M",A1)-3,2)<MID(B1,FIND("M",B1)-3,2))&" Y - "&MOD(MID(A1,FIND("M",A1)-3,2)-MID(B1,FIND("M",B1)-3,2),12)&" M"
Audere est facere
My logic is flawed.
I should have gone with QUOTIENT & MOD.
I tried all the Formulas, but I keep receiving the massage (The formula you typed contains an error).
I've attached the file that I tested my solution out on.
Hope this helps.
Pete
Perhaps your regional settings require semi-colon separators rather than commas.Originally Posted by Italian Job
Assuming your data in two adjacent cells in the same row (like A1:B1 as per your example) this formula might also work for you
=TEXT(DOLLARFR(SUMPRODUCT(MID(0&A1:B1,FIND({"Y";"M"},0&A1:B1)-3,2)*{12,-12;1,-1})/12,12)*100,"##"" Y - ""00"" M""")
I really appreciate your support, thank you so much. It is Okey now.
Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.
In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.
Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.
Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks