Convert quarters to years months and days
Cambouie44
Posted messages
4
Registration date
Status
Membre
Last intervention
-
Cambouie44 Posted messages 4 Registration date Status Membre Last intervention -
Cambouie44 Posted messages 4 Registration date Status Membre 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.
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 réponse
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.
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.
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
=$A$1*3-$C$1*12+INT($B$1/30)
the formula still doesn't work... and it gives me the #VALUE! message.
A1 =79
B1 = 77
C1 =INT(A1/4)
thank you very much for your help.
see you very soon surely.