Convert hours to months-days-hours
Solved
akaisii
Posted messages
150
Status
Membre
-
akaisii Posted messages 150 Status Membre -
akaisii Posted messages 150 Status Membre -
Hello,
I want to convert a number of hours into months-days-hours, and have the result in one cell.
I found some things about the subject on forums but it doesn’t work in my case, that is, when the number of months is less than 1.
For example, 600h and taking as a base, 1d = 24h
It should give 0 months 25 Days 10 Hours.
If anyone has an idea please.
Thank you in advance,
I want to convert a number of hours into months-days-hours, and have the result in one cell.
I found some things about the subject on forums but it doesn’t work in my case, that is, when the number of months is less than 1.
For example, 600h and taking as a base, 1d = 24h
It should give 0 months 25 Days 10 Hours.
If anyone has an idea please.
Thank you in advance,
6 réponses
600 hours does not equal 25 days and 10 hours!! It just makes 25 days.
600 / 24 = 25 !!
For months it's more complicated because the number of days varies from month to month. From what I can say, there should be a correspondence table that gives you the number of days per month. But even then, I'm not sure it would work.
In the meantime, here’s how to calculate the number of days and hours:
=ROUNDDOWN(A1/24;0) &" Days " & ((A1/24) - ROUNDDOWN((A1/24);0)) * 24 &" Hours "
I don’t know if that works for you.
600 / 24 = 25 !!
For months it's more complicated because the number of days varies from month to month. From what I can say, there should be a correspondence table that gives you the number of days per month. But even then, I'm not sure it would work.
In the meantime, here’s how to calculate the number of days and hours:
=ROUNDDOWN(A1/24;0) &" Days " & ((A1/24) - ROUNDDOWN((A1/24);0)) * 24 &" Hours "
I don’t know if that works for you.
Hello
in cell A1 use the format hh:mm:ss
for example
600:00:00
(equivalent to the date of 25/01/1900 00:00:00)
this formula
=(MONTH(A1)-1)" months "&DAY(A1)" day(s) "&HOUR(A1)" hour(s)."
will return
0 months 25 days() 0 hour(s)
3457:00:00 (23/05/1900 01:00:00)
will return
4 months 23 days 1 hour
so it may be off by 1 or 2 months depending on the number of days/months
Michel
in cell A1 use the format hh:mm:ss
for example
600:00:00
(equivalent to the date of 25/01/1900 00:00:00)
this formula
=(MONTH(A1)-1)" months "&DAY(A1)" day(s) "&HOUR(A1)" hour(s)."
will return
0 months 25 days() 0 hour(s)
3457:00:00 (23/05/1900 01:00:00)
will return
4 months 23 days 1 hour
so it may be off by 1 or 2 months depending on the number of days/months
Michel
Thank you for your answers.
My apologies. In my example, I meant 610 hours.
To generalize for now, I'm basing this on 1 month = 30 days.
Benoit A. > This formula does not give months if the number of days exceeds 30 days.
For example: 800 hours gives 33 days 8.00000000000006 hours, instead of 2 months and 2 days 8 hours.
michel_m > Indeed, it works for this example but it is not generalizable because in other cases it gives inaccurate values.
For example: 5000 hours gives 6 months 26 days 8 hours, instead of 7 months and 26 days 8 hours.
Otherwise, what I've done so far is to apply the cell formatting mm "months and" jj "days" h "hours" and it works partially, and the problem is that there shouldn't be less than 30 days.
My apologies. In my example, I meant 610 hours.
To generalize for now, I'm basing this on 1 month = 30 days.
Benoit A. > This formula does not give months if the number of days exceeds 30 days.
For example: 800 hours gives 33 days 8.00000000000006 hours, instead of 2 months and 2 days 8 hours.
michel_m > Indeed, it works for this example but it is not generalizable because in other cases it gives inaccurate values.
For example: 5000 hours gives 6 months 26 days 8 hours, instead of 7 months and 26 days 8 hours.
Otherwise, what I've done so far is to apply the cell formatting mm "months and" jj "days" h "hours" and it works partially, and the problem is that there shouldn't be less than 30 days.
Hello,
michel_m > Indeed, it works for this example, but it's not generalizable because in other cases it gives inaccurate values.
For example: 5000 hours gives 6 months, 26 days, 8 hours, instead of 7 months and 26 days, 8 hours.
You're not consistent with yourself.
Earlier you said 1 month = 30 days of 24 hours
With 5000/24/30 = 6.9444.
How can you find 7 months and 26 days???
The same goes for 800 hours. 800/24/30 = 1.111
How do you expect to find 2 months and 2 days?
eric
--
By continuously trying, we eventually succeed.
So the more it fails, the more chances we have that it will work. (the Shadoks)
In addition to the thank you (yes, really, it’s done!!!), remember to mark it as resolved. Thank you.
michel_m > Indeed, it works for this example, but it's not generalizable because in other cases it gives inaccurate values.
For example: 5000 hours gives 6 months, 26 days, 8 hours, instead of 7 months and 26 days, 8 hours.
You're not consistent with yourself.
Earlier you said 1 month = 30 days of 24 hours
With 5000/24/30 = 6.9444.
How can you find 7 months and 26 days???
The same goes for 800 hours. 800/24/30 = 1.111
How do you expect to find 2 months and 2 days?
eric
--
By continuously trying, we eventually succeed.
So the more it fails, the more chances we have that it will work. (the Shadoks)
In addition to the thank you (yes, really, it’s done!!!), remember to mark it as resolved. Thank you.
Hello,
eric
By continuously trying, you eventually succeed.
So the more it fails, the more chances you have that it works. (the Shadoks)
In addition to thank you (yes, it can be done !!!), remember to mark as resolved. Thank you.
=INT(A2/24/30)" months "&INT(MOD(A2/24;30))" days "&MOD(A2;24)" hours"
eric
By continuously trying, you eventually succeed.
So the more it fails, the more chances you have that it works. (the Shadoks)
In addition to thank you (yes, it can be done !!!), remember to mark as resolved. Thank you.