Date status column

[JoeHazelton]

Dear Community,

I am looking to create a column which indicates the status of each task which covers all eventualites. I am using four date columns, Start Date, Finish Date, Actual Start & Actual Finish.

The Status' i am looking to create are; Complete, In Progress, Planned, Late Starting & Late Finishing. So far i have managed to get the below working:

In progress

I think this Status needs to only show 'In Progress' when the Actual Start date is populated but i am happy with the above formula as this gives me a good idea of what is In progress even if the programme managers havent updated the Actual Start Date field.

Planned

Complete

For this entry i am looking to show the status as Complete once all four date columns have been populated with dates.

Late Starting

For this entry i am looking to show the Status as Late Starting when the Start Date is passed todays date and the Actual Start field is blank/empty.

Late Finishing

For this i am looking to show the Status as Late Finishing when the Actual Start date is populated but the Finish Date is passed today date.

Any advice/guidance on this would be greatly appreciated. Happy to share more information if required.

Regards

Joe

[DimaMD · Accepted solution]

@JoeHazelton I tried to simulate the example data, look maybe it will lead you to the right idea in solving your problem

measure = 
SWITCH( 
    TRUE(),
    [Start Date] > TODAY(), "Planned",
    AND([Start Date], [Finish Date]) && AND([Actual Start], [Actual Finish]) <> BLANK(), "Complete",
    AND([Start Date] <= TODAY(), [Actual Start] = BLANK()),  "Late Starting", 
    "Late Finishing"
      )

[JoeHazelton]

Wow! DimaMD..that totally works!. It covers every single possible date that my report throws out without any gaps..I cannot thank you enough.

Kind Regards

Joe

[DimaMD]

Hi, @JoeHazelton  It would be great if you could give an example of the data