Hi, please help with the following formula to return a text status:

JeanMarcKruger

I am trying to return the following status updates: "Planned" if Today's date is before the Planned start date, "Late" if Today's date is after the Due date, and "In progress" if Today's date is between the Planned start date and Due date. It works for "Planned" and "Late", but as soon as I add the "In progress" part it gives #Invalid operation error. Here is the formula I used:

=IF(TODAY() < [Planned start date]@row; "Planned"; IF(TODAY() > [Due date]@row; "Late"; IF([Planned start date]@row < TODAY() < [Due date]@row; "In progress")))

Nick Korna

Hi @JeanMarcKruger,

Your final part of the formula would need to be an IF(AND) statement to work:

=IF(TODAY() < [Planned start date]@row, "Planned", IF(TODAY() > [Due date]@row, "Late", IF(AND([Planned start date]@row < TODAY(), TODAY() < [Due date]@row), "In Progress")))

How do you want to treat things which are due to finish today though - late or in progress?

If "Late", then you can amend the formula to:

=IF(TODAY() < [Planned start date]@row, "Planned", IF(TODAY() >= [Due date]@row, "Late", IF(AND([Planned start date]@row <= TODAY(), TODAY() <= [Due date]@row), "In Progress")))

If "In progress", then you can simplify the formula even further if there are no further variables:

=IF(TODAY() < [Planned start date]@row, "Planned", IF(TODAY() > [Due date]@row, "Late", "In Progress"))

Sample results:

Hopefully this gives you some options to choose from - which one you would like to use is your choice.

If you've any questions, just ask.

Nick Korna

Hi @JeanMarcKruger,

Your final part of the formula would need to be an IF(AND) statement to work:

=IF(TODAY() < [Planned start date]@row, "Planned", IF(TODAY() > [Due date]@row, "Late", IF(AND([Planned start date]@row < TODAY(), TODAY() < [Due date]@row), "In Progress")))

How do you want to treat things which are due to finish today though - late or in progress?

If "Late", then you can amend the formula to:

=IF(TODAY() < [Planned start date]@row, "Planned", IF(TODAY() >= [Due date]@row, "Late", IF(AND([Planned start date]@row <= TODAY(), TODAY() <= [Due date]@row), "In Progress")))

If "In progress", then you can simplify the formula even further if there are no further variables:

=IF(TODAY() < [Planned start date]@row, "Planned", IF(TODAY() > [Due date]@row, "Late", "In Progress"))

Sample results:

Hopefully this gives you some options to choose from - which one you would like to use is your choice.

If you've any questions, just ask.

JeanMarcKruger

This worked perfectly, thank you very much:)