STATUS UPDATE - Based on Date

Options
eventsagp
eventsagp ✭✭✭
edited 06/09/24 in Formulas and Functions

Hello,

I want the process wherein once we click on the task status for example:

In Progress then start and end date are added, time time line status will read it immediately if it is "ON TIME" or "DELAYED" based on the end date.

If we click on the completed - and the end date is not yet on the past, then it will ON TIME - but if click on completed and the end date is in the past already it will be delayed.

I tried workflow but it's hard, i dont know where to start - i tried =IF([END DATE]@row < TODAY(1), "ON TIME", "") this function but i want it to be depending on task status and end date.

Can anyone help me on this one?

Thanks.

Best Answer

  • Protonspounge
    Protonspounge ✭✭✭✭✭
    edited 06/10/24 Answer ✓
    Options

    Hello @eventsagp,

    Would a nested IF statement help you here… you could set up the statement to be: -

    1. If [Task Status] is "Complete", then display "Completed" in [Timeline Status].
    2. If [Task Status] is "In Progress" and [End Date] is greater than today, then display "On Time" in [Timeline Status]
    3. If is [Task Status] is "In Progress" and [End Date] is less than today display "Delayed" in [Timeline Status].

    In the formula below, if none of the conditions are met, then [Timeline Status] will be blank.

    =IF([Task Status]@row = "complete", "Completed", IF(AND([Task Status]@row = "In Progress", [End Date]@row > TODAY()), "On time", IF(AND([Task Status]@row = "In Progress", [End Date]@row < TODAY()), "Delayed", "")))

Answers

  • Protonspounge
    Protonspounge ✭✭✭✭✭
    edited 06/10/24 Answer ✓
    Options

    Hello @eventsagp,

    Would a nested IF statement help you here… you could set up the statement to be: -

    1. If [Task Status] is "Complete", then display "Completed" in [Timeline Status].
    2. If [Task Status] is "In Progress" and [End Date] is greater than today, then display "On Time" in [Timeline Status]
    3. If is [Task Status] is "In Progress" and [End Date] is less than today display "Delayed" in [Timeline Status].

    In the formula below, if none of the conditions are met, then [Timeline Status] will be blank.

    =IF([Task Status]@row = "complete", "Completed", IF(AND([Task Status]@row = "In Progress", [End Date]@row > TODAY()), "On time", IF(AND([Task Status]@row = "In Progress", [End Date]@row < TODAY()), "Delayed", "")))

  • eventsagp
    eventsagp ✭✭✭
    edited 06/11/24
    Options

    Thank you @Protonspounge!

    It worked, but I have inquiry if you can help me on it.

    For example - Start Date was June 6 and End Date was June 9 - I finish the task today June 11, so I changed the task status to completed but it was delayed, how can I make it COMPLETED and "DELAYED" on the timeline status?

    I have another inquiry, it will be great if you can help me on it. If also task status is NOT STARTED or CANCELED, It will reflect on the timeline status.

    Thank you!

  • Protonspounge
    Protonspounge ✭✭✭✭✭
    edited 06/11/24
    Options

    Hello @eventsagp,

    I am pleased my previous answer worked for you and I am grateful for the opportunity to help you out 😁

    In order to achieve the "Completed and Delayed" status you are looking for we will need to be able to record the date at which the [Task Status] was changed to complete. We can achieve this using the "Record the date when specified criteria are met" automation.

    To make this a robust solution, I would set your [Task Status] column to a Dropdown List Column type. You may have it set up like this already but if not I would recommend it to avoid the automation not triggering if a user was to mis-spell "Complete"

    We will need to set up a column where the completed date will be recorded, in my example below I called it [Completed Time Stamp]. Once set up you can always hide it if you don't want it visible.

    We will then need to set the automation to record the date in [Completed Time Stamp] when [Task Status] is changed to complete.

    From the Automation menu at the top, select "Create workflow from template" and you are looking for this one under the Sheet Changes section.

    We want to set it up as per below - When [Task Status] changed to "Complete" record a date in [Completed Time Stamp]

    This automation will trigger as soon as [Task Status] is changed to "Complete" and the sheet is saved. With the date recorded we can use this column to highlight if the task was completed after the end date but adding the following statement into the previous formula - IF(AND([Task Status]@row = "Complete", [Completed Time Stamp]@row > [End Date]@row)

    For your second point, to add Not Started and Cancelled with can just add in the extra IF statements in to the previous formula with - IF([Task Status]@row = "Not Started", "Not Started", IF([Task Status]@row = "Cancelled", "Cancelled"

    Putting everything together you can the following formula should do everything you need if you put it in [Timeline Status]. (Note - If you call the [Completed Time Stamp] column something different, change the column in bold below accordingly)

    =IF(AND([Task Status]@row = "Complete", [Completed Time Stamp]@row > [End Date]@row), "Completed and Delayed", IF([Task Status]@row = "Complete", "Completed", IF(AND([Task Status]@row = "In Progress", [End Date]@row > TODAY()), "On time", IF(AND([Task Status]@row = "In Progress", [End Date]@row < TODAY()), "Delayed", IF([Task Status]@row = "Not Started", "Not Started", IF([Task Status]@row = "Cancelled", "Cancelled", " "))))))

    I hope that is helpful to you in some way,

    Protonspounge

  • eventsagp
    eventsagp ✭✭✭
    Options

    Hello @Protonspounge ,

    Once again thank you so much! I was so happy that it worked.

    I changed the Completed to Completed and On Time.

    =IF(AND([TASK STATUS]@row = "Complete", [COMPLETED TIME STAMP]@row > [END DATE]@row), "Completed and Delayed", IF([TASK STATUS]@row = "Complete", "Completed and On Time", IF(AND([TASK STATUS]@row = "In Progress", [END DATE]@row > TODAY()), "On time", IF(AND([TASK STATUS]@row = "In Progress", [END DATE]@row < TODAY()), "Delayed", IF([TASK STATUS]@row = "Not Started", "Not Started", IF([TASK STATUS]@row = "Cancelled", "Cancelled", " "))))))

    But thank you so much!

    Meanwhile I have some questions.. please see comments on the image below. Is it possible?

    Merci!

  • Protonspounge
    Protonspounge ✭✭✭✭✭
    edited 06/11/24
    Options

    Hello @eventsagp, to get "On Time" in [Timeline Status] if the Task Status is "In Progress" and the [End Date] is equal to or greater than today, we modify your previous formula as per below (edit in bold)

    =IF(AND([Task Status]@row = "Complete", [Completed Time Stamp]@row > [End Date]@row), "Completed and Delayed", IF([Task Status]@row = "Complete", "Completed and On Time", IF(AND([Task Status]@row = "In Progress", [End Date]@row >= TODAY()), "On Time", IF(AND([Task Status]@row = "In Progress", [End Date]@row < TODAY()), "Delayed", IF([Task Status]@row = "Not Started", "Not Started", IF([Task Status]@row = "Cancelled", "Cancelled", " "))))))

    For you other question about getting the child status on the parent row, I would like to ensure you get the best practice solution for this and so will ask @Paul Newcome to comment. @eventsagp - Can you confirm if [Task Status] is a dropdown box or calculated status or other? That will help with working out the best solution.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Here's another option for a formula (personal preference and character count are the only real differences):


    =IF(OR([Task Status]@row = "Not Started", [Task Status]@row = "Cancelled"), [Task Status]@row, IF([Task Status]@row = "In Progress", IF([End Date]@row < TODAY(), "Delayed", "On Time"), IF([Completed Time Stamp]@row > [End Date]@row, "Completed and Delayed", "Completed and On Time")))

    As for getting the child row statuses to roll up, we would need whatever logic you want to use for each of the different statuses.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!