SOLVED

SharePoint Calculated Column Date IF/Then

Copper Contributor

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)

 

THough335_0-1691183356673.png

 

5 Replies

@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

@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
best response confirmed by THough335 (Copper Contributor)
Solution

@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.

@ganeshsanap Thank you sooooo much for your assistance!  It worked.  Yay!

1 best response

Accepted Solutions
best response confirmed by THough335 (Copper Contributor)
Solution

@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.

View solution in original post