STATUS UPDATE - Based on Date

eventsagp

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.

Protonsponge

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

Protonsponge

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

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!

Protonsponge

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

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!

Protonsponge

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

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.

eventsagp

Thank you very much @Protonspounge , really a great help for our team.

Thank you too @Paul Newcome , regarding the child row statuses please see the image below.

Is it okay to have a formula instead of dropdown? I'm open to ideas.

Thank you in advance!

eventsagp

Any update?

Paul Newcome

You only have two outputs listed for the parent rows in your last screenshot (in progress and completed). Are there other statuses for the parent rows? There are a number of other statuses in the child rows. We would need the logic for each of the statuses to be output on the parent rows unless you wanted to try to tackle it yourself.

In that case you would use another nested IF statement with a COUNTIFS to count how many times each of the other statuses are listed in the CHILDREN() range and have your output based on that.

eventsagp

Hello @Paul Newcome,

Below are the task statuses, it is a drop down list.

Not Started
In Progress
Completed
Cancelled

is there a possible solution for it?

Protonsponge

Hello @eventsagp -

Below is one option that could work for you that involves the use of a [% Complete] and a [Status Helper Column]. [Status Helper Column] could always be hidden.

In the example below, within the [Task] Column is a parent row with sub-tasks, I think you are already set up like this.

In the [Task Status] column, I have a formula as per below asking:-

1. If the [Status Helper Column] is 0, then display "Not Started",
2. else if the [Status Helper Column] is 1 then display "Complete",
3. for anything else, display "In Progress"

=IF([Status Helper Column]@row = 0, "Not Started", IF([Status Helper Column]@row = 1, "Complete", "In Progress"))

The [Status Helper Column] is set up with the formula below asking:-

1. if the count of children is greater than 0, (e.g. has child rows), then average the [% Complete] column for the child rows. (This averages the % complete values for the child rows to give an overall % completion.)
2. else if the count of child rows is zero, then display the value in [% Complete] for that row.

=IF(COUNT(CHILDREN()) > 0, AVG(CHILDREN([% Complete]@row)), [% Complete]@row)

This set up involves tracking the % complete for each task in the [% Complete] Column and all is working as per the demos below.

I hope that is helpful to you in some way.

Protonspounge

Protonsponge

Maybe just for completeness and to link everything back to your initial question, and to also include "Cancelled" in to the solution, I added my above answer to your original set up:-

Adding in "Cancelled" the solution:-

If you write "Cancelled" in the [Task Status] column when a project is cancelled and change the formula in [Status Helper Column] to the one below, that will give Cancelled status at child and parent rows for that project. To avoid spelling errors, you could have the column as a drop down box and define "Cancelled" as the only option and then for everything else just type the % complete.

=IF(COUNTIFS(CHILDREN([Status Helper Column]@row), CONTAINS("cancelled", @cell)) > 0, "Cancelled", IF(COUNT(CHILDREN()) > 0, AVG(CHILDREN([% Complete]@row)), [% Complete]@row))

You will also need to change the formula in [Task Status] to include "Cancelled" with the one below:-

=IF([Status Helper Column]@row = "Cancelled", "Cancelled", IF([Status Helper Column]@row = 0, "Not Started", IF([Status Helper Column]@row = 1, "Complete", "In Progress")))

I believe, that together with the previous automation discussion, you have everything on your list 😀

Again, I hope some of the above is helpful to you in some way,

Protonspounge

Paul Newcome

And which column is this going in exactly? Can you provide a screenshot that shows column headers and hierarchy, and explain in detail what the various criteria are for each of the different statuses?