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
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
Rob