Excel - IF formula with Text and Date range to return text

[Bluebug_Rae]

I need a formula column M to reflect CRITICAL, CURRENT, FUTURE based on text and/ date range within column K.

Column K
[TABLE="width: 112"]
<tbody>[TR]
[TD]1-Aug-2018[/TD]
[/TR]
[TR]
[TD]31-Oct-2012[/TD]
[/TR]
[TR]
[TD]31-Oct-2014[/TD]
[/TR]
[TR]
[TD]Ended[/TD]
[/TR]
[TR]
[TD]31-Oct-2020[/TD]
[/TR]
[TR]
[TD]31-Jun-2017[/TD]
[/TR]
[TR]
[TD]30-Oct-2017[/TD]
[/TR]
[TR]
[TD]None Announced[/TD]
[/TR]
[TR]
[TD]30-Mar-2018[/TD]
[/TR]
[TR]
[TD]TBD[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
If Column K contain:
<12/31/2016, Ended should be marked as CRITICAL in column M
>= 1/1/2017, <= 12/31/2017 should be marked as CURRENT in column M
> 1/1/2018, TBD, None Announced should be marked as FUTURE in column M

What is my formula for column M?

 

[Bluebug_Rae]

BTW... Please and thank you.

I assume it will be an array, but need help; it is only recognizing about half of my formula.

Column K = [@EoSWM]

=IF([@EoSWM]<= 12/31/2016, "CRITICAL",IF(AND([@EoSWM]>="1/1/2017"+0,[@EoSWM]<="12/31/2017"+0),"CURRENT", IF([@EoSWM]> 1/1/2018, "FUTURE", IF([@EoSWM]="None Announced", "FUTURE", IF([@EoSWM]= "TBD", "FUTURE", IF([@EoSWM]="Ended", "CRITICAL", ""))))))

 

[crewaustin66]

I would suggest having a play with using Conditional Formatting to color the cells. IF's are not going to cut it. If that doesn't work, you will need to write a macro to make this rather complex decision and return CURRENT, CRITICAL, or FUTURE.

 

[Bluebug_Rae]

Thank, but I already considered and then decided against Conditional Formatting.