I need to substract estimated hrs with Actual hrs

Rakesh Lavishetty

here I need an automated formual to substract estimated hrs with actual hrs

Ex: Estimated hrs - Actual hrs

est vs act.jpg

Please suggest how to accomplish this, Thanks in advance.

Bassam Khalil

Hi @Rakesh Lavishetty

Hope you are fine, please try the following formula and convert it to a column formula:

=VALUE(LEFT([Actual Hours]@row, LEN([Actual Hours]@row) - 1)) - VALUE((LEFT([Estimated Hours]@row, LEN([Estimated Hours]@row) - 1)))

the following screenshot show the result

Danielle Arteaga

Hi, Rakesh. You should be able to just subtract the Actual Hours from the Estimate Hours, but because your data is a combination of numbers and letters, the "math" won't work. Can you remove the "h" from the data entries in each cell so that you have only a numeric value? Once you do that, this formula should work:

=[Actual Hours]@row - [Estimated Hours]@row

I'd put the formula in a new column called Variance (or something similar) and set it as a column-level formula.

Rakesh Lavishetty

Hi Danielle,

Thanks for your time writing in me.

I m really sorry but have to put the formula using "h".

Please suggest the formula which can be worked with both numerical and alphabates.

thanks,

Rakesh.

Bassam Khalil

Hi @Rakesh Lavishetty

Hope you are fine, please try the following formula and convert it to a column formula:

=VALUE(LEFT([Actual Hours]@row, LEN([Actual Hours]@row) - 1)) - VALUE((LEFT([Estimated Hours]@row, LEN([Estimated Hours]@row) - 1)))

the following screenshot show the result

Rakesh Lavishetty

Hi @Bassam.M Khalil ,

Hope you are doing well,

Thanks for your time to writing to me.

The formula which you are suggested working perfectly fine.

Thanks,

Rakesh.

Bassam Khalil

@Rakesh Lavishetty

You are welcome and am happy it works, Please help the Community by marking it as the accepted answer. It will make it easier for other Smartsheet Community members to find this solution or help to answer their questions.