0

I've created list that has 11 status column with date for each status.

Then I'm trying to add a current status column that should display the value of the status for which date has been filled.

Status 1, status 1 date, status 2, status date, etc. are column headings. Then I have Current Status column.

Now if status 1 date is not filled, it should display position started. Else check if status 2 date is not filled, then current status should display status 1 column value. And son for all 11 statuses. 11th status date filled, then should display status 11 value.

2
  • What is the question? If you are looking for formulas to help creating the calculated column, then please check this Microsoft reference: support.office.com/en-us/article/… – UBK Apr 30 '20 at 21:19
  • How do I create the formula to display current status value in the above situation? – user90557 Apr 30 '20 at 22:33
0

Try to create a formula with nested IF conditions. Something like following:

=IF(
    ISBLANK([status 1 date]), 
        "position started",
        IF(
            ISBLANK([status 2 date]), 
                [status 1], 
                IF(
                    ISBLANK([status 3 date]), 
                        [status 2], 
                        ...
                )
        )
    )
1
  • Perfect, this is exactly what I needed. Thank you so much for your help UBK, much appreciate your time and effort in answering my question. – user90557 May 1 '20 at 3:40
0

Calculated column is a way to do this just like UBK shows. However I will suggest you to use a Flow to update the status column according to your 11 columns. As the you have so many conditions and the nested if formula will become too complex for you to troubleshoot or maintain.

3
  • Thanks for your response Jerry, solution provided by UBK worked like a charm. Thank you too for your help, much appreciate your time and effort in answering to my question. – user90557 May 1 '20 at 3:40
  • It is complicated (I had 1 parenthesis missing and drove me crazy lol) but it works., unless there is a better way to do, I'll stick with UBK's solution :-) – user90557 May 1 '20 at 3:52
  • @user90557 Glad to be help. As you have finished the task then it is fine. Here is an example for you. Try it in future if you are interested. sharepointmaven.com/… – Jerry_MSFT May 1 '20 at 7:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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