Connvert number into hour

Julie Barbeau

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

Debbie Sawyer

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

Debbie Sawyer

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

Julie Barbeau

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?

Debbie Sawyer

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

Julie Barbeau

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

Julie Barbeau

Oh I found my mistake! I forgot ":''

Thanks for your help :D

Debbie Sawyer

Yay, well done! 🤩

Julie Barbeau

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

Debbie Sawyer

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

Julie Barbeau

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

Debbie Sawyer

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