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)]))
  • What is the data type of your "Number of day(s)" column? – Ganesh Sanap Jul 1 '20 at 8:18

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
  • 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 Jul 1 '20 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 Jul 1 '20 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 Jul 1 '20 at 9:44
  • 1
    Thank you. It works now. I copy-pasted the exact value and then tried. – Nikhila Menon Jul 1 '20 at 10:14
  • Great, glad it worked for you. – Ganesh Sanap Jul 1 '20 at 10:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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