SharePoint calculated conditional formula to return zero if column is zero

1
votes

I have calculated date formula for Projected NVSMR column:

=DATE(YEAR([Last NVMSR])+[3yr], MONTH([Last NVMSR]), DAY([Last NVMSR]))

Which is great. But, I also want it to return zero if the 3yr column equals zero. I have tried different DATEDIFF's and just IF formulas and SharePoint returns #NUM for all members listed or Syntax error.

I am the awards monitor and only certain individuals are allowed a three year award. If they are not allowed a three year award I want to enter "0" and have "0" return in the Projected NVMSR column "0".

1
sharepoint

1 Answers

0
votes

What you need is IF(TEST,TRUE,FALSE) - see this SharePoint Calculated Column Cheat Sheet for quick reference and examples.

So

=IF([3yr]=0,0,DATE(YEAR([Last NVMSR])+[3yr], MONTH([Last NVMSR]), DAY([Last NVMSR])))

Or formatted for easy reading

=IF( [3yr] = 0,
     0,
     DATE(YEAR([Last NVMSR])+[3yr], MONTH([Last NVMSR]), DAY([Last NVMSR]))
   )