If statement to show complete, ongoing, next, and planned events

[ExcelFanatic]

I have a worksheet showing events and have created a statement to show whether the event is complete, ongoing, next, or planned.

=IF($C2="Ongoing","Next",IF($B3<today(),"complete",IF(and($a3<today(),$b3>TODAY()),"Ongoing","Planned")))

The formula works great if there is an event that is ongoing, however if there are not ongoing events, it shows all future events as "Planned".

If there is not a ongoing event, I want to show the next event after a completed event as "Next", and all events after as "Planned" .

A - Start Date
B - End Date
C - Status

Thanks

 

[konew1]

I have a worksheet showing events and have created a statement to show whether the event is complete, ongoing, next, or planned.

=IF($C2="Ongoing","Next",IF($B3<today(),"complete",IF(and($a3<today(),$b3>TODAY()),"Ongoing","Planned")))

Your stated formula is invalid, I think you mean
=IF($C2="Ongoing","Next",IF($B3=TODAY(),"Ongoing","Planned"))
Please confirm.
If you are using TODAY() as criteria to determine if an event is ongoing or planned and if today the condition B3=TODAY() is met then tomorrow the event status will revert back to what it was yesterday

 

[ExcelFanatic]

The formula in my post is not complete (not sure how that happened). Here is my formula:

IF($C2="Ongoing","Next",IF($B3<today(),"complete",if(and($a3<today(),$b3>TODAY()),"Ongoing","Planned")))</today(),"complete",if(and($a3<today(),$b3>

 

[ExcelFanatic]

Keeps removing part of my formula when I post. Will try something else to show the whole formula

 

[ExcelFanatic]

Your stated formula is invalid, I think you mean
=IF($C2="Ongoing","Next",IF($B3=TODAY(),"Ongoing","Planned"))
Please confirm.
If you are using TODAY() as criteria to determine if an event is ongoing or planned and if today the condition B3=TODAY() is met then tomorrow the event status will revert back to what it was yesterday

IF($C2="Ongoing","Next",IF($B3<TODAY(),"Complete",IF(AND($A3<TODAY(),$B3>TODAY()),"Ongoing","Planned")))

 

[ExcelFanatic]

IF($C2="Ongoing", "Next", IF($B3<TODAY(), "Complete", IF(AND($A3<TODAY(),$B3>TODAY()), "Ongoing", "Planned")))

 

[ExcelFanatic]

Not sure why I cant get the entire formula to post. It keeps removing part of it

 

[ExcelFanatic]

IF($C2="Ongoing","Next",IF($B3<TODAY(),"Complete",IF(AND($A3<TODAY(),$B3>TODAY()),"Ongoing","Planned")))

 

[ExcelFanatic]

Not sure why I cant post the formula. Any Ideas?

 

[ExcelFanatic]

=IF($C1="Ongoing","Next",IF($B2<TODAY(),"Complete",IF(AND($A2<TODAY(),$B2>TODAY()),"Ongoing","Planned")))