Help with writing a formula to show Completed and Past Due along with other status.

Elizabeth Taft

Hi there, I am able to get all the status to show correctly, except the 'Complete' and 'Past Due', I know its something simple and I am just not thinking of it, help is appreciated!

I am using a 'End Date' and '% Complete'

Formula used on the 3rd date down

=IF(OR([% Complete]@row = 0, ISBLANK([% Complete]@row)), "Not Started", IF(AND([End Date]@row < TODAY(), [% Complete]@row < 100), "Past Due", IF([% Complete]@row = 100, "Complete")))

Formula used on the 4th date down

=IF(ISBLANK([% Complete]@row), "Not Started", IF([% Complete]@row < 1, "In Progress", IF([% Complete]@row = 1, "Complete", IF(AND([End Date]@row < TODAY(), [% Complete]@row > 100, "Past Due")))))

Nic Larsen

I see another typo. I didn't close the AND agreement. Sorry about that:

=IF([% Complete]@row = 1, "Complete", IF(ISBLANK([% Complete]@row), "Not Started", IF(AND([End Date]@row < TODAY(), [% Complete]@row < 1), "Past Due", IF([% Complete]@row < 1, "In Progress"

Nic Larsen

Try:

=IF([% Complete]@row = 1, "Complete", IF(ISBLANK([% Complete]@row), "Not Started", IF(AND([End Date]@row < TODAY(), [% Complete]@row , 1, "Past Due", IF([% Complete]@row < 1, "In Progress"

Elizabeth Taft

Thank you so much for answering! I tried it and getting the below response now

Nic Larsen

Ooops, I left something off when I edited:

=IF([% Complete]@row = 1, "Complete", IF(ISBLANK([% Complete]@row), "Not Started", IF(AND([End Date]@row < TODAY(), [% Complete]@row < 1, "Past Due", IF([% Complete]@row < 1, "In Progress"

Elizabeth Taft

Is it copying and pasting correctly? I am getting the same response.

Nic Larsen

I see another typo. I didn't close the AND agreement. Sorry about that:

=IF([% Complete]@row = 1, "Complete", IF(ISBLANK([% Complete]@row), "Not Started", IF(AND([End Date]@row < TODAY(), [% Complete]@row < 1), "Past Due", IF([% Complete]@row < 1, "In Progress"

Elizabeth Taft

PERFECT!! it works, thank you!!