# Formula to show reading ages in YY/MM format

1. ## Formula to show reading ages in YY/MM format

Hi,

I am looking for a formula that gives me a reading age difference in years and months with the format Years/Months.

I have attached a TEST DATA file with some dummy data.

Could someone help me with a formula that takes the age at test data and compares it to the reading age data and calculates the reading age difference?

For example in row 2 of the sheet, the formula will calculate the reading age difference in the same format,

A B C
5/4 5/3 -0/1

Many thanks for taking a look!  Register To Reply

2. ## Re: Formula to show reading ages in YY/MM format

Are you required to use a YY/MM format? As currently set up, all your ages are text strings, so you are required to parse the text strings into something meaningful, subtract the result, then manipulate the result back into the YY/MM text string. Most of the difficulty is in the text manipulation. If you can avoid all the text manipulation, this may be easier.

The way I would do this would be to use a numeric YY.MM format. I can then use the DOLLARDE() function to convert to decimal years, perform the subtraction, then convert back to YY.MM using the DOLLARFR() function. If A2 were 5.04 (note the 2 digit month) and B2 were 5.03, my calculation could be =DOLLARFR(DOLLARDE(B2,12)-DOLLARDE(A2,12),12) and would result in -0.01.

Would something like that work for you, or are you required to use a YY/MM text string input and output.  Register To Reply

3. ## Re: Formula to show reading ages in YY/MM format

Just realized a mistake i made...correcting it now then will repost.

sorry  Register To Reply

4. ## Re: Formula to show reading ages in YY/MM format

Hi,

Unfortunately, I need to keep the YY/MM format as I have to upload this data into a DB and the field will only take the reading age in the YY/MM format (for example, 5/4 which equates to 5 Years and 4 Months).

Thanks for looking though.  Register To Reply

5. ## Re: Formula to show reading ages in YY/MM format

Hi,

Thanks for looking and although your attachment does exactly what I need it to do, the format needs to be YY:MM (as in 5/4 equates to 5 Years and 4 Months).

Thanks for looking!  Register To Reply

6. ## Re: Formula to show reading ages in YY/MM format

Maybe try at C2

=SUBSTITUTE(SUBSTITUTE(TEXT(DOLLARFR(SUMPRODUCT(ABS(LEFT(A2:B2,FIND("/",A2:B2)-1)*12)+MID(A2:B2,FIND("/",A2:B2)+1,2),{-1,1})/12,12),"0.00"),".0","/"),".","/")  Register To Reply

7. ## Re: Formula to show reading ages in YY/MM format

Hi,

Why do some of the '5/3' entries in the Reading Age column have a minus symbol in front of them? Is that a typo? Assuming it is:

=SUBSTITUTE(TEXT(MMULT(MMULT({12,1},0+MID(A2:B2,{1;3},{1;2})),{-1;1})/12,"0\/0/12"),"/12","")

Regards  Register To Reply

8. ## Re: Formula to show reading ages in YY/MM format

Hi,

The minus symbol is correct. It just means the person's reading age is below the threshold of the test.

Many thanks for looking at this for me.

Regards  Register To Reply

9. ## Re: Formula to show reading ages in YY/MM format

Hi,

Your formula works a treat! Many thanks for looking at this for me.

Much appreciated!

Regards,  Register To Reply

10. ## Re: Formula to show reading ages in YY/MM format

Another one
Combine with XOR LX Text with fraction =SUBSTITUTE(TEXT(SUMPRODUCT(ABS(MID(SUBSTITUTE(A2:B2,"/",REPT(" ",8)),{1;9},8)*{12;1})*{-1,1})/12,"0\/0/12"),"/12",)  Register To Reply

11. ## Re: Formula to show reading ages in YY/MM format

Hi, Bo_Ry.

That's perfect. This one also fits the bill. Thanks for looking at an alternative for me as well.

Regards,  Register To Reply