Forum Discussion
SharePoint Calculated Column Date IF/Then
I have a SharePoint list with three date columns. Resubmit Date can have a value OR Extend Date can have a value. Based on which column has a value I would like that column to minus 14 days from that date in the Combined Date column. I have tried several different formulas but nothing seems to work.
This formula gives me correct value for Extend Date but not for Resubmit Date
=IF(ISBLANK(OR([Resubmit Date])),[Resubmit Date]-14,[Extend Date]-14)
nimesht Thanks for pointing out the error. One closing bracket was misplaced.
Here is corrected formula (also corrected in above response now):
=IF(ISBLANK([Resubmit Date]),IF(ISBLANK([Extend Date]),"",[Extend Date]-14),[Resubmit Date]-14)Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
5 Replies
THough335 Use this formula:
=IF(ISBLANK([Resubmit Date]),IF(ISBLANK([Extend Date]),"",[Extend Date]-14),[Resubmit Date]-14)Note: Sometimes comma( , ) does not work in formula (it is based on language or regional settings on your site). So in that case use semicolon( ; ) instead of comma( , ).
Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
- I see error in formula, nested parameters are not placed correctly so the last if has 4 parameters instead of 3
nimesht Thanks for pointing out the error. One closing bracket was misplaced.
Here is corrected formula (also corrected in above response now):
=IF(ISBLANK([Resubmit Date]),IF(ISBLANK([Extend Date]),"",[Extend Date]-14),[Resubmit Date]-14)Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
THough335 Did you try using max formula? i.e. Max of Resubmit Date and Extend Date like below
=max([Resubmit Date],[Extend Date])-14
If I am correct, you will have date in only 1 of the 2 columns, so max will return the one which is not blank, and then you can directly use it for calculation. This will be much cleaner compared to nested If/Else statements
