I have a SharePoint list with a completed date column. I wish to have a calculated column called status so when the completed date column has a date in it, it shows "Closed" and when it is blank it shows "Open".

Can anyone help me with the formula for this please?

2 Answers 2


Try using below formula, it will work for you:

=IF(ISBLANK([completed date]),"Open","Closed")


  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 Single line of text.


Column Settings:

enter image description here


enter image description here

official documentation:

  1. Calculated Field Formulas.
  • Thank you, the top one worked perfectly :-)
    – Andrea
    Feb 10, 2020 at 10:22
  • Welcome. Please Upvote(^) and accept as an Answer as it helped you. Feb 10, 2020 at 10:23
  • Will do :-) Can I ask how do I make the Open in Red and the Closed in Green?
    – Andrea
    Feb 11, 2020 at 9:59
  • 2
    If you are using modern experience then you can use Column/JSON formatting to achieve this. You can check the examples here or ask a new question so that I can answer on it with complete explanation and code. Feb 11, 2020 at 10:02

try the below formula, add the below formula in the status - calculated column

=IF( ISBLANK([completed]) , "Open" , "Closed" )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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