Probably an easy Excel Formula! can anyone help please!

[Carlos85]

 

 

Hi im not really an Excel person but need some help if anyone knows a formula to the below please, I've been looking online but the ones I've come across all relate to a full date 01/02/2021 ect.

 

Example: Theres two different ways i can write the equation.

 

6 Years, 2 Months (Difference) 7 Years, 8 Months =18 Months or 4.3(4Years/3Months) 6.9(6years/9months) =30 Months

 

Ideally I'd like the end answer/Solution to be in months if possible please

 

Thanks everyone!

[smithson83]

Hi, this is a good one...

 

The issue (as i see it) would be the written format and also that 7.8 is not 7 years 8 months (7.66 would be - ish).

 

So, and there may be a more concise or much better way of doing this that I've missed, but I would do it this way.

 

I have used SEARCH with Left and MID to find and strip out the numbers to the left of Years and Month/s, multiplied the years by 12 then added it to the Months, then subtracted the second cell from the first.

 

=CONCAT(((LEFT(C2,SEARCH(" Years",C2)-1)*12)+MID(C2,SEARCH(" Month",C2)-2,2)-((LEFT(B2,SEARCH(" Years",B2)-1)*12)+MID(B2,SEARCH(" Month",B2)-2,2)))," Months")

 

 

Hope this helps (or someone finds a cleaner way for you)

 

 

EDIT: Had to adjust the formula to account for double digits. Should be ok now (eg 7 Years, 10 Months was being seen as 7 Years 0 Months) - Fixed now

Edited March 26, 2021 by smithson83

[smithson83]

Ok, I deleted this first time as it didnt seem to work as well. but i think i have it down now.

 

You should be able to use the below Formula provided you format the 7.10 (years.months) as text. it wont make the formula any shorter, but will reduce data input.

 

=CONCAT(((LEFT(C7,(SEARCH(".",C7)-1))*12)+(MID(C7,SEARCH(".",C7)+1,2)))-(((LEFT(B7,(SEARCH(".",B7)-1))*12)+(MID(B7,SEARCH(".",B7)+1,2))))," Months")

 

[Ditto]

There may be a different solution - a couple of questions first. This appears to be progress measure calculation - is that the case? I do recall seeing something where a reading test (Suffolk Reading Score) was taken and it gave a measure that was effectively a 'reading age'. I think Toe-by-toe have something similar! i.e. are we dealing in a measure that is exclusively months (which can of course be converted to years and months). Also, if this is related to an age or a day date, such as birth date, it doesn't allow a pure mathematical conversion to months, not least because 'a month' is a different number of days depending on which month (and year if you take leap years in to account!) it is.

 

Essentially, if you can have the unit values as an integer number of months, a formula along the following lines would work.

=CONCATENATE(INT(A2/12)," Years ",MOD(A2,12), " Months") where A2 contains the number of months being converted.

Edited March 27, 2021 by Ditto

[Carlos85]

Hi all, thank you so much for all your help, I've tried to use the formula but I cant seem to get it to work. I keep getting an #NAME? Again sorry im not an Excel person! Can anyone just point me in the right direction! thanks

[Steve21]

Think you're mixing up two formulas posted above. The one you have in your screenshot is the one related to the 7.8 style as it's looking on the left of the "." which doesn't exist on what you screenshotted as you've got it on the months/years one, that's a different formula

 

Steve

[Carlos85]

Hi thanks for the quick reply, I realise now what you mean about the different formulas, I've tried the same formula in the correct place with the same result.

[Ditto]

Try changing CONCAT to CONCATENATE - I think only newer version of excel support the shorter version. I'm not sure this will fully sort things, but you might get #VALUE, which will actually be progress. Could you repost what you have at this point. Edited March 28, 2021 by Ditto

[Ditto]

A useful tip, under Formula (version dependent) there is an 'Evaluate Formula' which is a sort of function debugger. It can often highlight the problem as it steps through your code.

[Steve21]

Hi thanks for the quick reply, I realise now what you mean about the different formulas, I've tried the same formula in the correct place with the same result.

 

You'll have to show us what you've got in there exactly, As if you typed the same in both it'll still point to the wrong one even etc

 

Steve

[Ditto]

For inspiration and perseverance...