Using formula from duration/work columns

RFedders

Hello,

I am trying to create my own allocation % formula on my project plan sheet. I am utilizing a work column and a duration column. The work column represents how many hours of hands on the keyboard work that a task requires to be completed. Duration is the amount of days a task takes to be completed. My team enters their work and duration in relative to hours and days (ex. work value is 1h, duration value is 1d). They have 6 hours of allocated project work each day.

The current formula I am using is =(Work@row / Duration@row * 6) * 100 , however, this results in an #INVALID OPERATION.

Is there a formula I can use that only pulls the numerical value from the cell?

Genevieve P.

Hi @RFedders

How are you inputting the value in the Work column? Does it actually have the "h" alongside the number, or is it just "1" in the cell? If you're only using numbers in that column, are you using a formula to output it?

I agree that it sounds like you need to only take the numerical value of both the Work and Duration columns. I tested and you should be able to use the Duration directly as a number, which indicates to me that it's your Work cell that's being seen as text.

If there is no "h" with the number, you could simply wrap it in a VALUE Function to make sure it's a number:

=(VALUE(Work@row) / Duration@row * 6) * 100

However if you have the "h" in the cell, we'll need to use the LEFT Function to just take out the number from your text string:

=(VALUE(LEFT(Work@row, 1)) / Duration@row * 6) * 100

If this still doesn't work, it would be helpful to see a screen capture of your sheet but please block out sensitive data!

Cheers,

Genevieve

RFedders

Thank you for the help. I created a column dedicated to a FIND formula to pull out the "h" from the work column.

This is the new formula: =(VALUE(LEFT(Work@row, Find@row)) / (Duration@row * 6))