Please help - How to get how many years/months/days/hours/minutes/seconds are xxx hours if i watch only 4 hours per day

[mathkkad]

I would very much appreciate if you could help me to gather a formula on this issue:

Let`s say i have a number of hours like 213 hours 29 minutes 0 seconds, meaning 213:29:00 in a excel cell (but it can vary it can be 1246:32:00 hours or 12:00:00 hours and so on)
How can i get how many years/months/days/hours/minutes/seconds it will take to watch them in a constant rhythm of 4 hours per day (or 6 hours per day or 8 hours per day)
Maybe you know how to make a formula where i can vary the input of number of hours and the constant rhythm of watching per day and get a end result format of years/months/days/hours/minutes/seconds
"0y 0m 0d 0:00:00"

example:

Input (hours): 3:00:00 / Rhythm (hours per day): 04:00:00 / Time to complete (in years/months/days/hours/minutes/seconds): 0y 0m 0d 3:00:00
Input (hours): 4:00:00 / Rhythm (hours per day): 04:00:00 / Time to complete (in years/months/days/hours/minutes/seconds): 0y 0m 1d 0:00:00
Input (hours): 5:00:00 / Rhythm (hours per day): 04:00:00 / Time to complete (in years/months/days/hours/minutes/seconds): 0y 0m 1d 1:00:00
Input (hours): 7:00:00 / Rhythm (hours per day): 04:00:00 / Time to complete (in years/months/days/hours/minutes/seconds): 0y 0m 1d 3:00:00
Input (hours): 8:00:00 / Rhythm (hours per day): 04:00:00 / Time to complete (in years/months/days/hours/minutes/seconds): 0y 0m 2d 0:00:00
and so on...

Input (hours): 5:00:00 / Rhythm (hours per day): 06:00:00 / Time to complete (in years/months/days/hours/minutes/seconds): 0y 0m 0d 5:00:00
Input (hours): 6:00:00 / Rhythm (hours per day): 06:00:00 / Time to complete (in years/months/days/hours/minutes/seconds): 0y 0m 1d 0:00:00
Input (hours): 7:00:00 / Rhythm (hours per day): 06:00:00 / Time to complete (in years/months/days/hours/minutes/seconds): 0y 0m 1d 1:00:00
Input (hours): 11:00:00 / Rhythm (hours per day): 06:00:00 / Time to complete (in years/months/days/hours/minutes/seconds): 0y 0m 1d 5:00:00
Input (hours): 12:00:00 / Rhythm (hours per day): 06:00:00 / Time to complete (in years/months/days/hours/minutes/seconds): 0y 0m 2d 0:00:00
and so on...

Input (hours): 7:00:00 / Rhythm (hours per day): 08:00:00 / Time to complete (in years/months/days/hours/minutes/seconds): 0y 0m 0d 7:00:00
Input (hours): 8:00:00 / Rhythm (hours per day): 08:00:00 / Time to complete (in years/months/days/hours/minutes/seconds): 0y 0m 1d 0:00:00
Input (hours): 9:00:00 / Rhythm (hours per day): 08:00:00 / Time to complete (in years/months/days/hours/minutes/seconds): 0y 0m 1d 1:00:00
Input (hours): 15:00:00 / Rhythm (hours per day): 08:00:00 / Time to complete (in years/months/days/hours/minutes/seconds): 0y 0m 1d 7:00:00
Input (hours): 16:00:00 / Rhythm (hours per day): 08:00:00 / Time to complete (in years/months/days/hours/minutes/seconds): 0y 0m 2d 0:00:00
and so on...

Can you please help me find a formula that gives an answer in this format "0y 0m 0d 0:00:00"
Thank you!

 

[jasonb75]

Try this formula, Input hours in A2 and rhythm hours in B2

=DATEDIF(0,A2/B2,"y")&"y "&DATEDIF(0,A2/B2,"ym")&"m "&DATEDIF(0,A2/B2,"md")&"d "&TEXT(MOD(A2/B2,1)*B2,"hh:mm:ss")

 

[mathkkad]

Thank you so much for the answer. It seems to be the right solution. Thank you again!

 

[jasonb75]

The only thing to watch out for is a slight variation in the length of a month due to the formula using 1/1/1900 as a starting point.

 

[mathkkad]

Is there a way to make it use the month as a constant 30 days?

 

[jasonb75]

There is, but this will mean 360 day years, not 365. Not an issue over small time frames, but ~8400 hours with a 4 hour rhythm means the results vary by a month between the 2 methods.

=INT(A2/B2/360)&"y "&INT(MOD(A2/B2,360)/30)&"m "&INT(MOD(A2/B2,30))&"d "&TEXT(MOD(A2/B2,1)*B2,"hh:mm:ss")

I don't think that there will be such a thing as a 'perfect solution' for what you need, it will be more a case of finding the one that comes closest.

 

[mathkkad]

True. Thank you again for your help and time. I also appreciate how you pointed out the upsides and downsides exactly on point like a pro. It helped a lot. I will try and use the formulas on a case to case basis like you said as it`s the best way to get the most out of them and also use sometimes both to get an estimate on both methods.

 

[jasonb75]

You're welcome

I did try the formula with (365/12) to get a consistent and more accurate number of days in a month but the results were not as I expected. In theory, this should be the most accurate result, with only leap days throwing it off. I'm going over the logic again to see if I can find why it is not as expected.

 

[jasonb75]

So I figured out that the error was in my testing method not the formula itself, however there is an issue with the results due to rounding that makes it unusable.
Personally, I would use the formula from 2 for most things, there could be a day or 2 discrepancy if the month part of the result is 1 or 2 due to feb being used in the calculation, beyond that it should level out fairly well.