Multiple if conditions formula help

[rqadri]

Dear all,

I need your help for a formula I don't understand how to create. I used to work in an excel file with the formula but the file is not existing anymore and I'm not able to re create the formula by myself for the new file unfortunately.

The file looks like this:

I'm trying to create a formula for the column "RAG" which automatically depending on the values on the columns "Status" and "Due Date" give me either the text "Future Review" "Due Soon" "Overdue" or "Done.
So the formula should go like this: If "Due Date" is >Today+5 or more then "RAG" should give me "Future Review", if however "Due Date" is bigger than Today but smaller/equal 5 then it should show me "Due Soon" and if the "Due Date" is smaller than Today then it should show me "Overdue". Additionally the moment the column "Status" is set to any of the "Closed" types "RAG" should switch to "Done".

I hope it does not sound too complicated for you.

Thank you very much in advance and kind regards!

 

[Beneindias]

=IF(OR($B2="Closed Type A"; $B2="Closed Type B"; $B2="Closed Type C");"Done";IF($E2>TODAY() + 5;"Future Review";IF(AND($E2>TODAY();$E2<=TODAY() + 5);"Due Soon";IF($E2<TODAY();"Overdue"))))

Try this one. Probably you can make it look for Closed in the begining of a string, but I don't know how to do it

 

[ExceLoki]

=IF(OR($B2="Closed Type A"; $B2="Closed Type B"; $B2="Closed Type C");"Done";IF($E2>TODAY() + 5;"Future Review";IF(AND($E2>TODAY();$E2<=TODAY() + 5);"Due Soon";IF($E2<TODAY();"Overdue"))))

Try this one. Probably you can make it look for Closed in the begining of a string, but I don't know how to do it

think this is what he was meaning

=IF(LEFT(H2,6)="Closed","Done",IF(K2>TODAY() + 5,"Future Review",IF(AND(K2>TODAY(),K2<=TODAY() + 5),"Due Soon",IF(K2<TODAY(),"Overdue"))))

 

[Beneindias]

think this is what he was meaning

=IF(LEFT(H2,6)="Closed","Done",IF(K2>TODAY() + 5,"Future Review",IF(AND(K2>TODAY(),K2<=TODAY() + 5),"Due Soon",IF(K2<TODAY(),"Overdue"))))

I need to learn those tricks.

 

[rqadri]

=IF(OR($B2="Closed Type A"; $B2="Closed Type B"; $B2="Closed Type C");"Done";IF($E2>TODAY() + 5;"Future Review";IF(AND($E2>TODAY();$E2<=TODAY() + 5);"Due Soon";IF($E2<TODAY();"Overdue"))))

Try this one. Probably you can make it look for Closed in the begining of a string, but I don't know how to do it

Thank you! That's the formula I was looking for. It's working perfectly!

 

[Beneindias]

Thank you! That's the formula I was looking for. It's working perfectly!

You're welcome.

But @ExceLoki's solution looks better.

 

[rqadri]

You're welcome.

But @ExceLoki's solution looks better.

It looks better but somehow didn't work. But yours is working and it looks like the formula from the excel file I used to work in. So it's fine if I just use yours