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.

Kendra Michaels, CAPM

UPDATE:

=IF(AND([1st Scheduled]@row < TODAY(), [Completed date]@row = ""), "Overdue", IF([1st Scheduled]@row < [Completed date]@row, "Completed Late", IF(OR([1st Scheduled]@row > [Completed date]@row, [1st Scheduled]@row = [Completed date]@row), "Complete", IF(AND([1st Scheduled]@row > TODAY(), [Completed date]@row = ""), "In Progress", "Need Scheduling"))))

This will currently work for Overdue, Complete, and Completed Late. I'm still messing with In Progress and I added Need Scheduling for if the 1st scheduled column is blank.

dave.mcpherson56751

Hi

Thank you for taking the time to reply, its the first time i have attached a screen shot, so here goes. Hope it visible?

Dave

dave.mcpherson56751

Hi,

 

I think i failed with the screen shot, may i share the sheet with you?

 

Regards

 

Dave

dave.mcpherson56751

Hi

Would it be possible to not have the In Progress and Need scheduling but replace them with "Not due" if date is less than today?

Sorry to keep bothering you and i really do appreciate your help and time.

Thank you

Dave

Kendra Michaels, CAPM

Hi Dave, fixed it!

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

Make sure your column names exactly match what's in the formula, but you can adjust the name either in the column or the formula. As long as they match!

• If 1st Scheduled is in the past and completed date is blank: Overdue
• If completed date is after 1st scheduled: Completed Late
• If completed date is the same as or before 1st scheduled: Completed
• If completed date is blank and 1st scheduled is today or in the future: Not due

dave.mcpherson56751

You are amazing, that works perfectly.

Thank you so much for the time you have taken to make this work.

If you are ever in the UK I owe you a large glass of wine!

Thanks again Dave