Connvert number into hour
Hi,
I convert duration (in days) in to hour. Then I apply % of allocation to have a realistic working hours. By example, task with a duration of 11days, convert in 88 hours (8 working hours/day).
If my % of allocation is 5%, the answer is : 2,8 hours. It's a decimal number, how can I convert it into real hours?
The results I'm looking for is 1.5hr  1:30 min
Thanks
Best Answer

If they were all different on each row then you would need to break the formula down for the totals.
Instead of totalling the original duration column and multiplying it by %Allocation in the final formula. Do an extra column where the duration is worked out (I called mine Task Duration) then the Total Duration is SUMming the TaskDuration instead of the original Duration, which then takes into accoung the different % Allocation on each row. The last formula is then shortened to remove the % allocation part (as it is already included now in the task duration column!)
So here I have removed the part of the %allocation from the BLUE formula and put that bit in the Pink formula which feeds the Yellow formula! 😁😜
(I would then hide the Task Duration and Total Duration columns...)
Does this make sense!?
Kind regards
Debbie
Answers

Hello Julie
This formula takes duration in days, converts that 1 day to 8 hrs and uses a column called % Allocation which is formatted as a % but can be set to any value.
=INT((Duration@row * 8) * [% Allocation]@row) + ":" + (((Duration@row * 8) * [% Allocation]@row) * 60  (INT((Duration@row * 8) * [% Allocation]@row) * 60)) + " min"
This works for me  I hope it works for you too :)
Kind regards
Debbie

Hi Debbie
Thanks for your answer :) The problem I see is that even when the results is 2hours it's shown 2 min (see attached) How can I changed it to 2h00min by example?

Can you copy and paste your formula in please so I can compare it to mine?
Thanks
(I have not changed my formula from above and if I test it against a 5 day duration at 5% allocation it comes back as 2:0 mins (which is 2 hrs and 0 mins)
If you wanted it to show Hrs in the outcome you could change it to:
=INT((Duration@row * 8) * [% Allocation]@row) + "hrs :" + (((Duration@row * 8) * [% Allocation]@row) * 60  (INT((Duration@row * 8) * [% Allocation]@row) * 60)) + " mins"
Hope this helps

=INT((Durée6 * 8) * [% allocation]6) + (((Durée6 * 8) * [% allocation]6) * 60  (INT((Durée6 * 8) * [% allocation]6) * 60)) + "min"

Oh I found my mistake! I forgot ":''
Thanks for your help :D

Yay, well done! 🤩

Other question, How can I addition them? Looks what it shows if I add cells together

Yes the "+" in this formula is to "concatenate" or "put next to" so lets say this column is called "Answer" if in the cell Answer1 you have put =Answer2+Answer3+Answer4 then you will get the value that is in your top cell in your screen shot.
You would need to use the SUM function on the original values then apply this formula to the results  like this:
The yellow cell is the function behind the number 21 and the blue cell is the function behine 8hrs:24 mins
(which is 5% of 21 days)
Hope this helps
Debbie

I'm not sure it works if % allocation is different for every row? Which one I should use in the blue cell?

If they were all different on each row then you would need to break the formula down for the totals.
Instead of totalling the original duration column and multiplying it by %Allocation in the final formula. Do an extra column where the duration is worked out (I called mine Task Duration) then the Total Duration is SUMming the TaskDuration instead of the original Duration, which then takes into accoung the different % Allocation on each row. The last formula is then shortened to remove the % allocation part (as it is already included now in the task duration column!)
So here I have removed the part of the %allocation from the BLUE formula and put that bit in the Pink formula which feeds the Yellow formula! 😁😜
(I would then hide the Task Duration and Total Duration columns...)
Does this make sense!?
Kind regards
Debbie
Help Article Resources
Categories
Check out the Formula Handbook template!