1

I am working on creating a leave tracker list. For that, I have added a column for Leave Type with 4 choices with 1 choice being company off.

I want to add another column that says => Comp offs left which should calculate the difference between the total number of comp off lefts, i.e. 12-Number of days(s) leave requested for if the leave type is comp off.

I tried to use simple if statement but getting the value as No. I tried using int function and that too does not work.

=IF([Leave Type]="Company off (In lieu of)",12-int([Number of day(s)]))
Improve this question
1
  • What is the data type of your "Number of day(s)" column?
    – Ganesh Sanap - MVP
    Commented Jul 1, 2020 at 8:18

1 Answer 1

Reset to default
0

Try using this:

=IF([Leave Type]="Company off (In lieu of)", 12 - VALUE([Number of day(s)]), 0)
  1. Sometimes comma(,) does not work in formula (I am not sure but it is based on something language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).
  2. Return your calculated field as Number.

official documentation:

  1. Calculated Field Formulas.
  2. VALUE function
Improve this answer
7
  • Thanks for this. I tried using this formula but the values returns 0 all the time and not the difference. And I did try using semicolon as well but that resulted in syntax error.
    – Nikhila Menon
    Commented Jul 1, 2020 at 9:23
  • The logic behind the formula is: If Leave Type column is equal to "Company off (In lieu of)" then get 12 - Number of days else it will return 0. As you are getting 0 everytime, I will suggest you to create a new list item which will have Leave Type="Company off (In lieu of)".
    – Ganesh Sanap - MVP
    Commented Jul 1, 2020 at 9:43
  • Also check you are passing the correct choice value (Company off (In lieu of)) in formula. it is case-sensitive and it should be exactly same as it is given in column settings.
    – Ganesh Sanap - MVP
    Commented Jul 1, 2020 at 9:44
  • 1
    Thank you. It works now. I copy-pasted the exact value and then tried.
    – Nikhila Menon
    Commented Jul 1, 2020 at 10:14
  • Great, glad it worked for you.
    – Ganesh Sanap - MVP
    Commented Jul 1, 2020 at 10:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.