I need to create a calculation that basically is a subtraction between two fields (2 date fields). However it should be done only if a 3rd field is equal to a value of 5 or 6.

  • My first field is director approval date
  • My second field is finance approval date
  • My third field is either 5,6,7 or 8

I know this can be done in Excel but in my case I need this to be done in a calculated column in a SharePoint list.

  • So, apply your Excel skills, only limitation is SharePoint Formulas can not use all Excel functions. See this list for functions available in SharePoint – Danny '365CSI' Engelman Jul 14 '15 at 10:59

Formula will be something like this:

=IF(OR([Column3]="5", [Column3]="6"), [director approval date]-[finance approval date], "-")

Here is the reference Example of common formulas

  • Nice example, nice link. I didn't know about the OR function. Luis, you might like to check exactly what is in your source columns using IF and a test column. Test for 5 and output "yes" or "no" etc. – MikeAinOz Jul 14 '15 at 22:27
  • Hi Luis, check now. After adding 5 and 6 in double quotes (for e.g. "5", "6") the formula started working. I edited the formula in above answer. I tested it and its working. – P S Jul 15 '15 at 5:53
  • yes, now it works – Luis Carvalho Jul 15 '15 at 8:59
  • what if I want to add the following an additional condition? My fourth field is less than 500 – Luis Carvalho Jul 15 '15 at 9:16
  • The formula will be some thing like this: =IF(AND([Column4]<"500",OR([Column 3]="5",[Column 3]="6")),[Date 2]-[Date 1],"-") – P S Jul 15 '15 at 10:14

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.