I don't know if what I am trying to do is possible, but this is the theory.

I have a 3 columns in SharePoint

Project State - TEXT
Days Overrun - NUMBER
Actual Finish Date - DATE

enter image description here

The "Project State" column has three options: "Project Slip", "On Schedule" and "Complete". Then the project end date is greater than today the status is "On Schedule", then the project end date is less than todays date the status goes to "Project Slip". The formula I am using to do this part is:

=IF([Projected End Date]>TodaysDate-1,"On Schedule","Project Slip")

The third option "Completed" is only triggered when the "Actual Finish Date" has a value entered. The problem I am hitting is the IF statement appears to stop the third option being set as it has already matched a condition on the previous IF. I've tried to have a play around with the ordering of the IF statement but I can't get it to respond as I want.

First attempt:

=IF([Projected End Date]>TodaysDate-1,"On Schedule",IF([Projected End Date]<TodaysDate-1,"Project Slip",IF([Actual Finish Date])="",0,"Complete"))

Second attempt

=IF(ISBLANK([Actual Finish Date]),0,IF([Projected End Date]>TodaysDate-1,"On Schedule",IF([Projected End Date]<TodaysDate-1,"Project Slip")),"Complete")

Neither of those worked as I'd like.

Thanks in advance

2 Answers 2


Just run the condition for the third option first, use ISBLANK function to identify if “Actual Finish Date” is blank:

IF(NOT(ISBLANK([Actual Finish Date])), "Completed",
    IF([Projected End Date] > TODAY()-1,"On Schedule","Project Slip")

Reference: IS functions.

  • Perfect, thanks!
    – RobC_CTL
    Commented Nov 19, 2020 at 11:39

I am answering as per my understanding from your question (correct me know if I am wrong).

If you want to show the Project State as "Completed" when "Actual Finish Date" has some value then try below formula:

=IF(ISBLANK([Actual Finish Date]), IF([Projected End Date] > [TodaysDate]-1, "On Schedule", "Project Slip"), "Complete")

Assuming you have [TodaysDate] column in your list or you can use Today() instead.

Reference: Calculated Field Formulas

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.