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

Answers

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    edited 04/06/20

    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?


  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    edited 04/07/20

    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

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    Yay, well done! 🤩

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


  • Debbie Sawyer
    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

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!