Combining IF formulas to Return Text Values

t.siddall101151 · 08/22/19

I have 3 columns; Start Date, End Date, Progress

I can get the following formulas to work separately for row 17:

=IF([Start Date]17 < TODAY(), "In Progress", "Not Started") Returns in the Progress column

=IF([End Date]17 < TODAY(), "Overdue") Returns in the Progress column.

I want to combine these 2 formulas in one cell to return either; In Progress, Not Started or Overdue.

If there are no dates in the Start and End Date columns I want it to return blank in the Progress column.

Additionally I would like to use [Start Date]:[Start Date] instead of [Start Date]17 to cover the whole column, but it doesn't seem to work in either formula.

Thanks

Tom

Andrée Starå · 08/23/19

Hi Tom,

Try something like this.

This will show as blank if either the Start or End Date is blank.

=IF(OR(ISBLANK([Start Date]@row); ISBLANK([End Date]@row)); ""; IF([End Date]@row < TODAY(); "Overdue"; IF([Start Date]@row < TODAY(); "In Progress"; "Not Started")))

The same version but with the below changes for your and others convenience.

=IF(OR(ISBLANK([Start Date]@row), ISBLANK([End Date]@row)), "", IF([End Date]@row < TODAY(), "Overdue", IF([Start Date]@row < TODAY(), "In Progress", "Not Started")))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

t.siddall101151 · 08/23/19

Hello Andree,

It works perfectly.

Thanks for helping me and for the quick response.

Tom

Andrée Starå · 08/24/19

Excellent!

Happy to help!

Combining IF formulas to Return Text Values

Comments

Help Article Resources

Categories