View original (French)

Convert quarters to years months and days

Cambouie44 Posted messages 4 Registration date   Status Member Last intervention   -  
Cambouie44 Posted messages 4 Registration date   Status Member Last intervention   -
Hello
As part of my duties, I need to convert some data.
It mainly concerns the durations of quarters and contribution days for pensions (e.g., 79 quarters 77 days).
I need to convert this data into years, months, and days.

Do you have a formula that could help me?

Thank you in advance.

1 answer

  1.
    PHILOU10120 Posted messages 6463 Registration date   Status Contributor Last intervention   835
     
    Hello Cambouie44

    For the years 79 in A1
    =INT(A1/4) this gives 19 years
    for the months 77 in B1 for my example
    =(A1-E5*4)*3+INT(B1/30) this gives 11 months
    For the days
    =B1-INT(B1/30)*30 result 17

    or
    =INT(B1-INT(B1/(360/12))*365/12) result 16
    it depends on the precision month = 30 days or month = 365/12

    --
    It is by forging that one becomes a blacksmith. - It is at the foot of the wall that one sees the mason - one always learns from their mistakes.
    6
    1. Cambouie44 Posted messages 4 Registration date   Status Member Last intervention  
       
      Thank you very much Philou10120.

      I can tell you that the blacksmith will have work with me for 30 years!
      The proof....
      I entered the formulas as you gave them to me, but it doesn’t work for the months. I end up with a total of 239. Oops!!!

      In A2 = number of quarters = 79
      In B2 = number of days = 77
      In C2 = A2/A4 = years = 19,...
      In D2 = =(A2-E5*4)*3+INT(B1/30) = months = 239 !!!!!
      In E2 = days = B2-INT(B2/30)*30 = 17

      Where is it getting stuck?

      Thanks for your help.
      Best regards
      0
      1. PHILOU10120 Posted messages 6463 Registration date   Status Contributor Last intervention   835 > Cambouie44 Posted messages 4 Registration date   Status Member Last intervention  
         
        I forgot to include the $ so the formula is incorrect, I don't have a reference E5

        =$A$1*3-$C$1*12+INT($B$1/30)
        -1
      2. Cambouie44 Posted messages 4 Registration date   Status Member Last intervention   > PHILOU10120 Posted messages 6463 Registration date   Status Contributor Last intervention  
         
        Sorry, but I warned you, the blacksmith is going to have his work cut out for him!
        the formula still doesn't work... and it gives me the #VALUE! message.
        0
      3. PHILOU10120 Posted messages 6463 Registration date   Status Contributor Last intervention   835 > Cambouie44 Posted messages 4 Registration date   Status Member Last intervention  
         
        if you have #value it's because one of the info is text and not recognized as a value
        A1 =79
        B1 = 77
        C1 =INT(A1/4)
        0
    2. Cambouie44 Posted messages 4 Registration date   Status Member Last intervention  
       
      great !!! it works !!!

      thank you very much for your help.

      see you very soon surely.
      0