Calculate total experience - Restrict decimal to two values

Joseph Aloysias

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"

Is there a way we can calculate total exp? @Paul Newcome please help

Paul Newcome

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

Joseph Aloysias

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)

Paul Newcome

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

Paul Newcome

And 24.10 + 23.1 IS 47.2.

To get 47.11 you would have to have 24.10 + 23.01

Joseph Aloysias

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

Paul Newcome

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?

Joseph Aloysias

@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

Paul Newcome

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

Joseph Aloysias

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

Paul Newcome

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?

Joseph Aloysias

Yes exactly, I would like to calculate yy.mm

Paul Newcome

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))

Joseph Aloysias

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