View original (French)

1 an, 6 mois

EMI77 -  
Raymond PENTIER Posted messages 58219 Registration date   Status Contributor Last intervention   -
Hello everyone,

I am looking for a formula that allows me to convert a decimal number,
For example: when I enter 1.7 in a cell, I would like to have the result of 1 year and 7 months.

Thank you for your responses
Configuration: Windows XP Internet Explorer 7.0

6 answers

  1.
    chossette9 Posted messages 6855 Registration date   Status Contributor Last intervention   1 313
     
    Uh, that might be difficult.

    1 year and 7 months is not equal to 1.7 years, since there are 12 months in a year. So how do you represent a duration of 1 year and 11 months? 1.11? Which falls between 1.1 and 1.2, so between 13 and 14 months?

    I don't think it's possible, but since I don't have all the answers, maybe someone will provide the right answer to your question.

    Best regards.
    --
    If we had to lock all the idiots in cupboards,
    there would be no one left to close the doors.
    Once an idiot, always an idiot!
    0
    1. EMI77
       
      Hello,


      Thank you anyway.
      0
    2. EMI77
       
      Hello,


      Thank you anyway.
      0
  2. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Hello

    1.7 in A1
    for the year
    =INT(A1)
    for the months
    =MOD(A1,1)*10

    to make it nice
    =INT(A1)&IF(INT(A1)<=1," year and "," years and ")&MOD(A1,1)*10&" months"

    Edit 11:28 AM:
    To take into account the excellent comment from chossette
    =INT(A1)&IF(INT(A1)<=1," year and "," years and ")&IF(MOD(A1,1)<0.1,MOD(A1,1)*10,MOD(A1,1)*100)&" months"

    The remaining problem (and not just a little) is for month 10 converted ruthlessly into 1....
    --
    Best regards, Michel
    0
    1. EMI77
       
      Thank you,

      I tested the first formula, when I type 1.11 I get the result 1 year and 1.1 months and with the second formula when I type 1.11 I get the expected result of 1 year and 11 months but when I type 1.1 I get the result of 1 year and 10 months.
      Isn't there a solution that directly modifies the concerned cell
      I was told about custom in cell format but I couldn't find anything.


      Thank you
      0
  3. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Customization won't change anything about the problem...
    To do it right, it would be necessary to label 1.01, 1.02........ 1.10, 1.11 (and review the formula)
    But that may not depend on you....
    --
    Best regards, Michel
    0
  4. Raymond PENTIER Posted messages 58219 Registration date   Status Contributor Last intervention   17 485
     

    0" year(s)",0" month"
    --
    It's nice, retirement! Especially in the West Indies... :-)
    ☻ Raymond ♂
    0
  6. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    You can try assigning your cell the custom format:
    NULL
    --
    Michel
    0
    1. Raymond PENTIER Posted messages 58219 Registration date   Status Contributor Last intervention   17 485
       
      I don't recognize you, Michel!
      The tone and style...
      The content neither: Did you test my suggestion? I did, and it works!
      I'm even convinced that it's more effective than all the other proposals...
      --
      Retirement is great! Especially in the Caribbean... :-)
      ☻ Raymond ♂
      0
  7. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello,

    As Michel correctly pointed out, as long as you use the decimal separator, 1.1 will always equal 1.10

    For what you are asking to be possible, the input format needs to be changed.

    For example, use a ; or a space
    If you enter 1;1 then it will be a string that can be distinguished from 1;10 and your formula will be:
    =LEFT(A1;SEARCH(";";A1)-1) & " year" & IF(LEFT(A1;SEARCH(";";A1)-1)*1>1;"s ";" ") & MID(A1;SEARCH(";";A1)+1;2) & " months"

    If you need to use the decimal separator, then the decimal part can only represent a fraction of a year and nothing else, and this is the case if it's the result of a calculation.

    Eric
    0