# Calculate total experience - Restrict decimal to two values

Options
✭✭✭✭✭
edited 12/13/22

Hi All,

I'm trying to calculate overall experience from the "Date of Joining" column, I'm able to calculate days and convert it into years and months(Exp column), (Prev Experience column data enter manually) but when I sum the previous exp + exp column its showing "0"

• ✭✭✭✭✭✭
Options

The formula you have in your screenshot needs to be wrapped in a VALUE function.

• ✭✭✭✭✭
Options

I want to calculate total experience in years and months, but here this its showing like this, could you please help me with the right formula?

if I use sum formula its showing "0"

@Paul Newcome If I use value function calculations are right for single decimals. for double decimals 24.10 + 23.1 its showing as 47.2 which is supposed to be 47.11

Could you please what am I doing wrong here =VALUE([prev.Experience]@row) + VALUE(Exp@row)

• ✭✭✭✭✭✭
Options

Try clicking on the column header and then adjusting how many decimals are shown to increase it to two (right side of top toolbar).

• ✭✭✭✭✭✭
Options

And 24.10 + 23.1 IS 47.2.

To get 47.11 you would have to have 24.10 + 23.01

• ✭✭✭✭✭
Options

Now its calculating like this, we want to restrict the decimal values to .11(if it's beyond .11 its should calculate as number)

we want to have Year and month count in the total exp

• ✭✭✭✭✭✭
Options

I'm not sure I understand what you are looking for. It is pulling in the two decimals. Are you trying to round up to the next number if the decimal is greater than .11? What is it you are trying to do?

• ✭✭✭✭✭
Options

@Paul Newcome I'm trying to calculate total experience of a candidate from the date of joining(Date column), for example if a candidate joined on 06/11/2020, I want to calculate his/her work experience in years and months(in this case this would be 2 years, 2 month as on today)

and also I would like to add his previous experience(for example 2years11months) with the current experience which is 2 years 2months, So his/her total experience would be 5years 1month

• ✭✭✭✭✭✭
Options

But in your last screenshot, it looks like everything is calculating as it should.

• ✭✭✭✭✭
Options

in that screenshot some of them are showing 8.60, 3.70, 9.17 (its supposed to be in 12months limit)

• ✭✭✭✭✭✭
Options

So you are not looking for a number that represents the total number of years where 1 year and 6 months would be 1.5 (for 1 1/2 years)? Are you trying to get a number that is more like

yy.mm

Where in the above example it would be 1.06?

• ✭✭✭✭✭
Options

Yes exactly, I would like to calculate yy.mm

• ✭✭✭✭✭✭
Options

In that case you would need to use something more like

Exp Column Formula:

=(YEAR(TODAY()) - YEAR([Joining Date]@row) - IF(MONTH(TODAY())< MONTH([Joining Date]@row), 1, 0)) + "." + ((MONTH(TODAY()) - MONTH([Joining Date]@row)) + IF(MONTH(TODAY())< MONTH([Joining Date]@row), 12, 0))

Total Exp Column Formula:

=(VALUE(LEFT(Exp@row, FIND(".", Exp@row) - 1)) + VALUE(LEFT([Previous Exp]@row, FIND(".", [Previous Exp]@row) - 1)) + IF(VALUE(RIGHT(Exp@row, 2)) + VALUE(RIGHT([Previous Exp]@row))>= 12, 1, 0)) + "." + (VALUE(RIGHT(Exp@row, 2)) + VALUE(RIGHT([Previous Exp]@row)) - IF(VALUE(RIGHT(Exp@row, 2)) + VALUE(RIGHT([Previous Exp]@row))>= 12, 12, 0))

• ✭✭✭✭✭
Options

Thank you @Paul Newcome , but I get some error and wrong values in some rows,

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!