View original (French)

Convert numbers to months and days

Solved
zooldevorges Posted messages 12 Status Member -  
zooldevorges Posted messages 12 Status Member -
Hello,

I would like to convert a number (with a comma) into months and days.

Thank you in advance

zool

14 answers

  1.
    zooldevorges Posted messages 12 Status Member 13
     
    Hello everyone

    thank you for your help. I managed to finish my table and convert it into months and days.

    I will fall asleep less ignorant tonight, because I've learned a new formula.

    have a nice day
    zool.
    11
  2. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
     
    And finally, which solution did you choose?
    --
    Retirement is nice! Especially in the Caribbean... :-)
    ☻ Raymond ♂
    5
  3. zooldevorges Posted messages 12 Status Member 13
     
    re

    your formula with the separation of the whole number part and the decimal part (INT).

    thank you again
    enjoy your retirement

    zool
    2
  4. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Hi,

    can you provide an example of your file with the link below (paste the generated link in a next post), several possibilities depending on the result obtained from your operations

    https://www.cjoint.com/

    Best regards
    1
  6. triwix Posted messages 306 Status Member 22
     
    =INT(A1/24/365) & " year(s) " & INT((((A1/24/365) - INT(A1/24/365)) * 365) / (365/12)) & " month(s) " & INT(((((A1/24/365) - INT(A1/24/365)) * 365) / (365/12)) - INT((((A1/24/365) - INT(A1/24/365)) * 365) / (365/12))) * (365/12)) & " day(s) "

    Nice formula!
    --
    Patience is the mother of safety
    1
  7. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Good evening,

    convert a number (with a decimal) into months and days
    A number doesn't mean anything, you need to provide the unit of your number.
    Does it represent seconds? years? centuries?
    Then it will be up to you to arbitrarily set the duration of a month, for example, 1 month = 30.5 days.
    After that, there are just a few operations to perform...

    eric
    0
  8. zooldevorges Posted messages 12 Status Member 13
     
    Hello,

    my Excel table:

    VHE 40.67
    HHT 384.00

    Week 5.51 (40.67/384)*52
    Month 1.27 (=(5.51/52)*100)*0.12

    I would like to convert 1.27 into months and days.

    I hope that’s clearer.

    Thank you for your help
    zool
    0
  9. zooldevorges Posted messages 12 Status Member 13
     
    Hello

    my attached file.

    http://www.cijoint.fr/cjlink.php?file=cj200901/cijYP6FVWd.xls

    thank you for your help
    0
    1. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
       
      I start over:

      A calculation gives you a result of 1.27 months,
      that is to say 1 month and 0.27 months
      so 27 hundredths of 30 days
      which is 0.27*30=8 days

      Final result 1 month and 28 days.

      With Excel if you have your result 1.27 in B23 you need to separate the whole part and the decimal part:
      * In C27 the formula =INT(B23) which gives 1
      * In C28 the formula =(B23-C27)*30 which gives 8.1
      --
      Retirement is great! Especially in the Caribbean... :-)
      ☻ Raymond ♂
      0
  10. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Hi,

    Before entering the data, set the cells that will contain hours to the format

    [h]:mm:ss

    To do this, Format/cell/number tab/select custom then [h]:mm:ss or modify the format [h]:mm to omit the seconds

    See you!
    0
    1. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
       
      Ah! I forgot to mention in the previous post, when entering hours, the separator for hours, minutes, seconds in Excel is the colon: (if the regional settings have not been changed)

      so for ten fifteen for example enter 10:15

      See you later!
      0
    2. zooldevorges Posted messages 12 Status Member 13
       
      I want to convert the result of a calculation (decimal number) into months and days.
      0
  11. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    I would like to convert 1.27 into months and days
    Post 2 is still relevant for me...
    0
  12. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Hi,

    It's hard to understand your expectations, as Eriiic asked you in post2, please provide more explanations or attach an example as I advised you in post1, with annotations like in this cell I have this number and I want to get this figure etc.

    In the meantime, if your decimal number is in A1, try this formula in a cell that you've set to the custom format [h]:mm:ss

    =A1/24

    Without clarification, it will be impossible for me to continue this discussion.

    Talk later.
    0
    1. Ludivine
       
      Can I ask you something that has strictly nothing to do with the conversation?? lol!
      0
      1. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281 > Ludivine
         
        Hello,

        it's best to start a new post with your question.
        More people will read a new post...
        eric
        0
      2. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482 > Ludivine
         
        To get there, click on "Ask your question" at the top right of the screen, orange color.
        --
        It's great, retirement! Especially in the Caribbean... :-)
        ☻ Raymond ♂
        0
  13. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
     
    Hello eriiic, Mike-31, and the others.

    My post 14 proposal is hidden between 6 and 7 ...
    --
    Retirement is great! Especially in the Caribbean... :-)
    ☻ Raymond ♂
    0
    1. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      Hello Raymond,

      Personally, I always refuse to search as long as he hasn't said whether it's 1.27 months or something else...
      I don't want to be forced to analyze his formulas to be sure of the counting unit of his result.
      I'm giving up on this post.

      Have a good evening everyone.
      Eric
      0
  14. Raymond PENTIER Posted messages 58213 Registration date   Status Contributor Last intervention   17 482
     
    If it is not a digit (from 0 to 9) but a decimal number, and this number comes from an insertion, extraction, or calculation and is supposed to represent a date, simply apply the date format (or a custom format) to the cell.
    --
    Retirement is great! Especially in the Caribbean... :-)
    ☻ Raymond ♂
    -1
    1. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      Hi Raymond Erci etc...

      A little extra information on Raymond's conclusion.
      In the case of a decimal number, the typical format (example) 14/03/01 PM in the date formatting choice list also gives the corresponding time of day.

      It doesn’t add much except the opportunity to wish everyone a Happy New Year

      Best regards
      PS: Actually, I think the question is more about calculating a number of months and days than about the date, in which case Eric is right again for this year. But we still need to address the month of February!
      --
      Truth is not possessed, it is only sought.
      0
  15. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    RE,

    If in A1 you entered a decimal number such as 824.00, paste this formula directly into a cell, and you will get the number of years, months, days, and hours.

    If you have a total in hours for example in A2 you have 824:00 in A1 enter =A1*24 and set it to number format

    =INT(A1/24/365)" year(s) "&INT((((A1/24/365)-INT(A1/24/365))*365)/(365/12))" month(s) "&INT(((((A1/24/365)-INT(A1/24/365))*365)/(365/12)-INT((((A1/24/365)-INT(A1/24/365))*365)/(365/12)))*(365/12))" day(s) "&HOUR((A1/24)-(INT(A1/24)))" hour(s) "&MINUTE((A1/24)-(INT(A1/24)))" minute(s)"

    If you don't need the number of years, you can simplify

    =INT((((A1/24/365)-INT(A1/24/365))*365)/(365/12))" month(s) "&INT(((((A1/24/365)-INT(A1/24/365))*365)/(365/12)-INT((((A1/24/365)-INT(A1/24/365))*365)/(365/12)))*(365/12))" day(s) "&HOUR((A1/24)-(INT(A1/24)))" hour(s) "&MINUTE((A1/24)-(INT(A1/24)))" minute(s)"

    Sometimes formulas are altered during sending, if this is the case I will send you an example in an attachment

    See you+
    -1
    1. zooldevorges Posted messages 12 Status Member 13
       
      re

      I tried, but it's the number of hours and minutes that I don't want. How can I modify the formula?

      Thank you for your help

      Formulas and I are a bit at odds!!!
      zool
      0