Calculate total experience - Restrict decimal to two values
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
Answers
-
The formula you have in your screenshot needs to be wrapped in a VALUE function.
-
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)
-
Try clicking on the column header and then adjusting how many decimals are shown to increase it to two (right side of top toolbar).
-
And 24.10 + 23.1 IS 47.2.
To get 47.11 you would have to have 24.10 + 23.01
-
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
-
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?
-
@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
-
But in your last screenshot, it looks like everything is calculating as it should.
-
in that screenshot some of them are showing 8.60, 3.70, 9.17 (its supposed to be in 12months limit)
-
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?
-
Yes exactly, I would like to calculate yy.mm
-
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))
-
Thank you @Paul Newcome , but I get some error and wrong values in some rows,
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!