+ Reply to Thread
Results 1 to 11 of 11

Formula to show reading ages in YY/MM format

  1. #1
    Registered User
    Join Date
    10-19-2016
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    74

    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!
    Attached Files Attached Files
    Last edited by cosmica67; 10-01-2020 at 01:56 AM. Reason: Problem solved.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,645

    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.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    07-19-2019
    Location
    Illinois, USA
    MS-Off Ver
    365
    Posts
    65

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

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

    sorry
    Last edited by PrimePorkchop; 09-30-2020 at 02:32 PM.

  4. #4
    Registered User
    Join Date
    10-19-2016
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    74

    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.

  5. #5
    Registered User
    Join Date
    10-19-2016
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    74

    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!

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,468

    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","/"),".","/")
    Attached Files Attached Files

  7. #7
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    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
    Last edited by XOR LX; 09-30-2020 at 04:27 PM.
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  8. #8
    Registered User
    Join Date
    10-19-2016
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    74

    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

  9. #9
    Registered User
    Join Date
    10-19-2016
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    74

    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,

  10. #10
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,468

    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",)
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    10-19-2016
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    74

    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,

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Reading Age Problem when showing some Minus Ages
    By cosmica67 in forum Excel General
    Replies: 4
    Last Post: 11-03-2016, 06:45 AM
  2. [SOLVED] Reading Age Problem with minus Ages
    By cosmica67 in forum Excel General
    Replies: 6
    Last Post: 11-03-2016, 05:21 AM
  3. [SOLVED] Reading Ages Conversion
    By cosmica67 in forum Excel General
    Replies: 2
    Last Post: 10-21-2016, 07:42 AM
  4. [SOLVED] Reading ages problem in Excel
    By cosmica67 in forum Excel General
    Replies: 5
    Last Post: 10-19-2016, 01:53 PM
  5. [SOLVED] Reading ages problem in Excel
    By cosmica67 in forum Excel General
    Replies: 9
    Last Post: 10-19-2016, 07:12 AM
  6. formula to get ages
    By gfer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-19-2008, 09:32 AM
  7. [SOLVED] How to not show save dialog after reading from excel file.
    By philg1984 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-13-2006, 04:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1