Status update based on start/end date

mmac

Hello,

I'm looking to accomplish the following and am hopeful that someone will be able to help support. Thank you in advance.

Working with the following columns:

Start Date

End Date

Sale Status

Assignee

QA Status

Scenarios:

If a sale is live and has not yet come to an end date based on start date/end date columns, the sale status would = Active.

If a sale is no longer live based on start date/end date columns, the sale status would = Inactive

If the sale status = Inactive, the QA status would be blank (overwriting existing "Yes" check mark) and would signify having to start the QA status of take down efforts behind the sale itself. 

In addition to the above, if Sale Status = Planning, assign to Person A under the Assignee column (pulled from a contact list). If Sale Status = Implementation, assign to Person B under the Assignee column.

Video example of walk through: https://www.loom.com/share/f651fbb6083a4a79bf8770136f726235

Genevieve P.

Hi @mmac

It sounds like we might be missing some of the criteria to properly build all of these formulas. I'll outline the way to do exactly what you asked for, but this may not actually be the best way to achieve your results:

For the Status Column:

=IF([End Date]@row > TODAY(), "Active", IF([End Date]@row < TODAY(), "Inactive"))

This would change your column to only ever say one or the other, though, based on if the End Date is in the past or the future. This means you do not have any of the other options (Planning / Implementation / Scheduled) included in this formula.

For the QA status:

=IF([Sale Status]@row = "Inactive", "", 1)

This looks at the Sale Status column in that row and makes the checkbox blank if "Inactive" is selected. Otherwise, the box will be checked.

Keep in mind that you can either have a formula check the box or have the box be manually checked... you can't do both. For example, if this formula reads the Status as Inactive, but you want to check the box anyway, when you click the box it will overwrite and erase the formula. If there are other instances that you want this box checked or not, you would need to build that into this IF statement, so that you never need to manually adjust the result.

The same goes for the Sale Status column formula above... if you selected a different option from the drop-down, it would erase the formula.

For the Assignee column:

=IF([Sale Status]@row = "Planning", "firstemailaddress", IF([Sale Status]@row = "Implementation", "secondemailaddress"))

This will return a blank cell in the Assignee column unless one of these two Sales Status's is chosen.

Here are some Help Center articles that may help:

• Formulas, tips and tricks
• IF function
• TODAY function
• Formula FAQs

Let me know if you would like any further clarification after you have tried these formulas!

Cheers,

Genevieve

mmac

Hi @Genevieve P.

Thanks so much for the response and time spent on helping me.

Sale Status

Based on the criteria provided previously provided by me, I may not have been very clear. I'll do my best to clarify what I'm hoping to accomplish.

If start date / end date is in the future, sale status = inactive

If start date = today / end date is in the future, sale status = active

If start date is in the future / end date is BLANK, sale status = inactive

If start date = today / end date is BLANK, sale status = active

When I used the current formula it doesn't seem to be functioning correctly, or at least based on the above criteria.

I have also shared out the sheet that I am working with publicly.

https://app.smartsheet.com/b/publish?EQBCT=b06cec9ffa75410da3db2eb04ffffcfc

QA Status

To expand on the workflow a bit more, the vision is that a user is notified today once the sale status = implementation. That is in place today. The user would then have a QA list and then visit the QA Status and mark the dropdown as "yes" for a green check mark icon.

However, once the sale ends based on the end date cell and the Sale Status updates to Inactive, the QA Status cell that was previously marked as "Yes" with a green check mark icon, will then revert to a "No" a red X icon, or blank (either or). The user will receive an automated email and then the user will then QA and check off that cell as complete. This would signify that the inactive sale that is now over has been validated and they completed QA efforts.

Genevieve P.

Hi @mmac

Sale Status

Here is a formula that matches exactly what you specified, but it sounds like we still might be missing criteria:

=IF(AND([Start Date]@row > TODAY(), [End Date]@row = ""), "Inactive", IF(AND([Start Date]@row = TODAY(), [End Date]@row = ""), "Active", IF(AND([Start Date]@row <= TODAY(), [End Date]@row > TODAY()), "Active", IF(AND([Start Date]@row > TODAY(), [End Date]@row > TODAY()), "Inactive"))))

For example, this won't return anything if both dates are in the past, or if the start date is in the past and the end is blank. Keep in mind that Logic formulas like this read from left-to-right, and stop as soon as criteria is met.

In regards to your explanation, it sounds like you are looking to use this Sale Status column in two ways: manually choosing a drop-down option and having a formula return the result based on the dates. This column cannot do both...either you manually choose the status or you build a formula to return all of the results.

I would suggest having two columns. One would automatically return whether or not the row is "Active" with a formula. The other would have your other Status options to be manually selected (Planning / Implementation / Scheduled, and maybe Complete?).

Then you could create a workflow that when the Status column is manually changed to "Implementation", a notification is sent (as you have currently set up).

Next, the user manually checks a new column called "Initial QA" once they have completed their first QA checklist.

Then, you can send an automated Update Request when your new Inactive/Active column changes to "Inactive" and the initial QA checklist box is currently Checked (because otherwise this would send when the row is inactive for other reasons, such as the start date is in the future). This Update Request will ask the user to review the information, perhaps changing the "Sale Status" to a new status called Complete, and finally checking a second, new column called "Final QA", with a second check mark. (Click here to learn more about Automatic Update Requests).

You would need a second QA column to record this final review since you want the user to manually check the QA column the first time. This means that the QA column cannot use a formula to change to a "no" or red X based on other criteria because any formula would be overwritten with a manual change.

You could, however, set up Conditional Formatting rules on your sheet to make certain rows stand out if they say "Inactive" or if the QA boxes have not been checked yet. (Click here to learn more about Conditional Formatting).

If I'm missing any of the steps or criteria, please let me know!

Cheers,

Genevieve

mmac

@Genevieve P. Hello! Thank you again for your continued support in helping me narrow down the various use cases. Your suggestion in your last reply has got me thinking about a different approach altogether which may be more effecient for each user involved in the process.

Live column

The column currently has this formula populated which seems to work fine for the moment.

=IF(AND([Start Date]@row < TODAY(), [End Date]@row < TODAY()), "Red", IF(AND([Start Date]@row <= TODAY(), OR([End Date]@row > TODAY(), ISBLANK([End Date]@row))), "Green"))

Sale Status column

I have cleared out any formula for the time being and adjusted the dropdown options to only include the following:

Planning

Implementation

Scheduled

Additional thoughts are that once a sale goes live based on start date, the cell within the row under the Live column will turn green as it does today and then a notification will be sent to the user to then manually update the QA Status and check off the symbol as "Yes"

If the sale does have an end date, the end date will then cause the cell as part of the row under the Live column to turn red as it currently does. As part of this, I am thinking that the QA Status cell would then turn blank and the previously checked off symbol "Yes" would be cleared out. At the end of the sale, a notification would be sent to the user and they would visit the row and check it off once more.

In short, I think what I would be looking for at this point to place under the QA Status column/cell would be if end date is reached, the cell would be blank. 

In addition to the above, do you happen to have knowledge on if a specific time can be included as part of the formula I am using in the Live column? For example, the Live column turns to Red based on end date (which is fine), but it's not specific to the actual end time of the sale as part of that date. For example, if a sale ends today 1/27/20, the cell is already Red, which technically the sale ends at 11:59PM, PST on 1/27/20. Perhaps this warrants another date column to reflect properly? Or even a time column... not quite sure.

Genevieve P.

Hi @mmac ,

The first half of this sounds great!

However you're running into the same issue with the "Approval" checkmark. You're still looking for a way to have a user manually change the cell to be checked and to have a formula within that column then erase that cell afterwards... you have to choose either a formula or a manual check, not both.

The QA Status cell that your user changes to "Yes" could not automatically be cleared out. You will need to create two different "Yes" columns: one for the first manual check and one for the second, final check.

For your last, Time question, we do not currently have a Time function or column for what you're looking to do. That said, if you adjust your "Green" statement to look at if the end date is equal to or greater than today, then it will keep it Green until midnight that day. Try this:

=IF(AND([Start Date]@row <= TODAY(), OR([End Date]@row >= TODAY(), ISBLANK([End Date]@row))), "Green", IF(AND([Start Date]@row < TODAY(), [End Date]@row <= TODAY()), "Red"))

You will notice that I reorganized your statement to have "Red" at the end. Logic formulas read left-to-right, so your current formula would have read a blank cell as "less than today" first, which means anything with a blank cell as the end date would have turned Red instead of Green.

mmac

@Genevieve P You have been such a great help. Thank you for the suggestions and support you have provided!

Genevieve P.

No problem!