Forum Discussion

JoeHazelton's avatar
JoeHazelton
Frequent Visitor
4 years ago
Solved

Date status column

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's avatar
    DimaMD
    4 years ago

    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"
      )


3 Replies

  • DimaMD's avatar
    DimaMD
    Solution Sage
    4 years ago

    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's avatar
      JoeHazelton
      Frequent Visitor
      4 years ago

      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