Auto updating a status

dave.mcpherson56751

Hi,

I am looking for some help with a formula please?

I have a scheduled date which is static, Column name "1st Scheduled" i then have a completed date, Column name "Completed date"

I am trying to have the status column update to "Overdue" if no date is entered in the "Completed date" and today is past the "1st Scheduled" date.

Also want the status to change to "completed late" if the "completed date" is after the "1st scheduled" date.

Finally the status to read "Complete" if the the completed date is on or before the "1st Scheduled"

I am not even sure if this is possible and certainly above my ability at the moment, so would really appreciate any help.

Thank you 

 

Dave 

Kendra Michaels, CAPM

Some nested IF functions should do the trick! Try this:

=IF(AND([Completed Date]@row=""), [1st Scheduled]@row>TODAY()), "Overdue", IF([Completed date]@row>[1st Scheduled]@row, "Completed Late", "Complete"

I may have some of the > and < backwards, I can never remember the syntax of that with dates. Can you attach a screenshot of your sheet? Then I can get it perfect.