Excel formula to subtract yy-mm from another yy-mm
New here? Learn about Bountify and follow @bountify to get notified of new bounties! x

I have an Excel spreadsheet and a long list of numbers that represent years and months. For example 16-7 represents 16 years and 7 months.

    A          B

1 16-7 8-9

The cells are formatted in a custom format of yy-mm and I am looking for the formula/function to subtract the value of B1 from A1 to determine the remaining years, months. In the example above, A1-B1 would produce a result of 7-10 for 7 years 10 months.

How do I get there?

@Jay are the date cells formated as dates? Or are they just standard cells with quantities in them?
– alex almost 8 years ago
@alex, no, just standard cells with quantities.
– Jay almost 8 years ago
@Jay Is it okay if I use VBA?
– alex almost 8 years ago
@alex ideally, looking for something that can be done completely within a formula
– Jay almost 8 years ago
do you mean A1 minus B1 would equal 7-10? B1-A1 would seem to equal negative 7 years 10 months.
– skram almost 8 years ago
@skram yes, thanks for catching that. That's what I meant, editing for clarification now.
– Jay almost 8 years ago
OK - I've got the answer. Would you prefer it to be one big formula or would you like it the way I developed it which is a "saner" set of columns which ultimately gets you to the answer?
– skram almost 8 years ago
@skram One big formula would be great!
– Jay almost 8 years ago
awarded to skram
Tags
excel

Crowdsource coding tasks.

Sign up About

2 Solutions

Tip!
✓
Winning solution Tipped

This is it in two formulas. Doing it in one would make it more than twice as long as the first one below and make even Excel gurus cringe

In column C, to get A-B in months:

=((LEFT(A1,FIND("-",A1,1)-1)*12)+RIGHT(A1,FIND("-",A1,1)-LEN(LEFT(A1,FIND("-",A1,1)-1))))-((LEFT(B1,FIND("-",B1,1)-1)*12)+RIGHT(B1,FIND("-",B1,1)-LEN(LEFT(B1,FIND("-",B1,1)-1))))

In column D, to transform [A-B in months] to the YY-MM format:

=ROUNDDOWN(C1/12,0)&"-"&MOD(C1,12)

Let me know how it goes!

@skram Wow! I didn't know that that was possible without VBA... and for @Jay, I can confirm that it is working.
– alex almost 8 years ago
Here is a one liner of @skram's code: =ROUNDDOWN((((LEFT(A1,FIND("-",A1,1)-1)*12)+RIGHT(A1,FIND("-",A1,1)-LEN(LEFT(A1,FIND("-",A1,1)-1))))-((LEFT(B1,FIND("-",B1,1)-1)*12)+RIGHT(B1,FIND("-",B1,1)-LEN(LEFT(B1,FIND("-",B1,1)-1)))))/12,0)&"-"&MOD((((LEFT(A1,FIND("-",A1,1)-1)*12)+RIGHT(A1,FIND("-",A1,1)-LEN(LEFT(A1,FIND("-",A1,1)-1))))-((LEFT(B1,FIND("-",B1,1)-1)*12)+RIGHT(B1,FIND("-",B1,1)-LEN(LEFT(B1,FIND("-",B1,1)-1))))),12)
– alex almost 8 years ago
Beautiful solution, thank you Skram! And thank you Alex for combining into a single formula, is there a way to tip you as well?
– Jay almost 8 years ago
@Jay you don't have to, but I created an answer below (which is a repeat of my comment) so you can tip me if you want to.
– alex almost 8 years ago
I appreciate the work that both of you have done. Thanks for figuring this out!
– Jay almost 8 years ago
No problem - it was honestly kind of fun :)
– skram almost 8 years ago

Here is a one liner of @skram's code (above):

=ROUNDDOWN((((LEFT(A1,FIND("-",A1,1)-1)*12)+RIGHT(A1,FIND("-",A1,1)-LEN(LEFT(A1,FIND("-",A1,1)-1))))-((LEFT(B1,FIND("-",B1,1)-1)*12)+RIGHT(B1,FIND("-",B1,1)-LEN(LEFT(B1,FIND("-",B1,1)-1)))))/12,0)&"-"&MOD((((LEFT(A1,FIND("-",A1,1)-1)*12)+RIGHT(A1,FIND("-",A1,1)-LEN(LEFT(A1,FIND("-",A1,1)-1))))-((LEFT(B1,FIND("-",B1,1)-1)*12)+RIGHT(B1,FIND("-",B1,1)-LEN(LEFT(B1,FIND("-",B1,1)-1))))),12)
@Jay Thanks.
– alex almost 8 years ago
View Timeline