Formula to Handle SLA Due Date vs Campaign Start Date

Julia Voyvodich

Hi, Smartsheet Community! 👋 This is my first post. Thanks in advance to anyone offering help. Much appreciated! Lots of text ahead but I'm trying to include all relevant details and context.

SITUATION

We recently launched a new digital marketing request form. On the request form, there are fields for Campaign Type and Campaign Start Date. The request form feeds into a project tracker for the digital marketing team.

On the project tracker, there's an SLA Due Date field that auto-calculates the SLA Due Date based on Campaign Type and Request Date. For example, if a request is submitted on 8/10/23 and Campaign Type has a turnaround time of 5 business days then SLA Due Date would be 8/17/23.

There are also Status and Progress columns on the project tracker. Status options include: Request Submitted, Request Reviewed, Building Campaign, Campaign Live, Campaign Complete. The Status column is manually updated by the digital marketing team. The Progress column is automated by a formula and includes these options: On Track, At Risk, Done.

ISSUE

If Status is NOT Campaign Live or Campaign Complete AND the SLA Due Date has passed then Progress equals "At Risk." It highlights red to catch the attention of the digital marketing team. This setup normally works except when someone requests a project way before their desired Campaign Start Date. That's when we have issues.

EXAMPLE

Someone submitted a request on 07/28/23 with a desired Campaign Start Date of 09/14/23. This particular Campaign Type has an SLA of 5 business days from the request date so SLA Due Date is 8/4/23. The project isn't yet complete as of 8/10/23. Because it's not complete AND the SLA Due Date has passed, Progress equals "At Risk." It's highlighted red on the project tracker and looks bad. However, it's a false alarm. The requestor doesn't need it done until 9/14/23.

QUESTION

What type of formula could handle this situation? If SLA Due Date is in the past BUT Campaign Start Date has NOT passed then the Progress column should say "On Track." In other words, it's only "Off Track" if BOTH the SLA Due Date and Campaign Start Date are both in the past.

I'm also totally open to rethinking this entire workflow. Perhaps I'm overcomplicating things with two date fields for the digital marketing team to work off of. 😅

Paul Newcome

There's the issue. It works in the one standalone because you aren't referencing a cell with an error in it.

That error is pulling through though in the final formula because that formula is referencing the cell.

Whatever formula you have in that column, wrap it in an IFERROR.

=IFERROR(original_formula, "")

Danielle Arteaga

Can you add a column to your sheet (and form) called "Target Start Date"? This date would be populated by the requestor. Then, rather than calculating your SLA from the Request Date, you can calculate it from the Target Start Date. You will still capture the Request Date, so that you can run metrics as needed from that, but you will get a more accurate picture of your SLA compliance when measuring from a Target Start Date instead.

You might also want to add an "Actual Start Date" field, which your team populates, if you want to measure any slippage on your SLA.

Paul Newcome

So if I understand correctly... The issue is with the formula in the Progress column? What is that formula currently?

Julia Voyvodich

@Danielle Arteaga Thanks for your reply! I like your "Target Start Date" and "Actual Start Date" ideas. Is the below workflow what you were imagining?

On the project tracker, a formula would subtract the SLA from the "Campaign Start Date" to auto-generate "Target Start Date". The team would manually populate "Actual Start Date." The "Progress" column would automatically update based on "Status" and "Campaign Start Date."

@Paul Newcome Thanks for your reply! Yes, the original issue was the "Progress" column. Formula below.

=IF(OR(Status@row = "Campaign Live", Status@row = "Campaign Complete"), "Done", IF(Status@row = "Request Submitted", "Not Started", IF(AND(OR(Status@row = "Building Campaign", Status@row = "Request Reviewed"), [Days Until Due]@row <= 0), "At Risk", "On Track")))

Based on Danielle's response though, I'm starting to think the bigger issue is the "SLA Due Date" column. Perhaps my entire workflow needs an overhaul!

Danielle Arteaga

@Julia Voyvodich - when the form is completed, the user would manually enter the "Target Start Date." This addresses the use case you mentioned, where the user doesn't actually want you to start right away, but your formula calculates the SLA based on the date the form was submitted. Instead, you would use the [Target Start Date] to calculate the SLA. The [Actual Start Date] column can be used to determine how many of your projects start on time and to calculate the average variance (if any) between the [Target Start Date] and [Actual Start Date]. This may give you deeper insight into why you are meeting or not meeting your SLA. You can also calculate the average variance between [Request Date] and [Target Start Date] to get a sense of how far in advance your users are making their requests.

Paul Newcome

You should be able to just update your formula like so:

=IF(OR(Status@row = "Campaign Live", Status@row = "Campaign Complete"), "Done", IF(Status@row = "Request Submitted", "Not Started", IF(AND(OR(Status@row = "Building Campaign", Status@row = "Request Reviewed"), [Campaign Start Date]@row< TODAY()), "At Risk", "On Track")))

Julia Voyvodich

@Danielle Arteaga and @Paul Newcome Thank you both so much! I updated my workflow to incorporate both of your ideas.

NEW WORKFLOW

1. Requestor chooses Campaign Start Date on request form. (@Danielle Arteaga - We have 5 different SLAs depending on the campaign type so I automated "Target Start Date" instead of having the requestor fill it out. Figured this would eliminate confusion if the requestor chose a "Target Start Date" that's not within SLA.)
2. On the project tracker sheet where request form answers go, added a Request Reviewed? checkbox column for digital team. The SLA timer doesn't start until the digital team reviews the request and validates the required info.
3. Once the Request Reviewed? checkbox is checked, a hidden Project Start Date column records the date that the checkbox was checked. This starts the SLA timer.
4. Hidden SLA Due Date column adds SLA business days based on Project Start Date and Turnaround (Business Days) columns. Here's the SLA Due Date formula: =(WORKDAY([Project Start Date]@row, [Turnaround (Business Days)]@row))
5. Hidden Days Until Due column calculates...days until due. Here's the formula: =[Target Launch Date]@row - TODAY()
6. Target Launch Date column picks the higher date between Campaign Start Date and SLA Due Date. If the request is still being reviewed, it will show "Request Under Review" instead of a target date. Here's the Target Launch Date formula: =IFERROR(MAX([Campaign Start Date]@row, [SLA Due Date]@row), "Request Under Review")
7. Updated Progress column formula to incorporate these new columns. This is where I'm stuck now! Here's what I want the formula to do. If Status is "Live" or "Complete" then Progress = "Done." If Status is NOT "Live" or "Complete" AND Days Until Due < 1 then Progress = "At Risk." If Target Launch Date is "Request Under Review" instead of a date then Progress = "Request Under Review." All other scenarios would be Progress = "On Track."

FORMULA QUESTION

How do I fix the below formula so it does the following? If Target Launch Date is "Request Under Review" instead of a date then Progress = "Request Under Review." That's the broken part of the formula.

=IF(OR(Status@row = "Live", Status@row = "Complete"), "Done", IF(AND(Status@row <> "Live", Status@row <> "Complete", [Days Until Due]@row < 1), "At Risk", IF([Target Launch Date]@row = "Request Under Review", "Request Under Review", "On Track")))

Seems like Smartsheet isn't recognizing the text in the Target Launch Date column. However, it DOES recognize the text when I do a fresh formula in a test column. The below formula works on its own but not when combined in the above formula.

=IF([Target Launch Date]@row = "Request Under Review", "Request Under Review", "On Track")

Paul Newcome

We can start by simplifying the formula a bit.

=IF(OR(Status@row = "Live", Status@row = "Complete"), "Done", IF([Days Until Due]@row < 1, "At Risk", IF([Target Launch Date]@row = "Request Under Review", "Request Under Review", "On Track")))

From there we would need to see some examples of the formula not working along with all of the cells being referenced in the formula. When you say that it is not working, are you getting an error or an unexpected output?

Julia Voyvodich

@Paul Newcome Thanks for simplifying the formula! I'm still getting an #INVALID OPERATION error when Target Launch Date is "Request Under Review." Screenshot below.

Again, the weird thing is that the below test formula DOES work when Target Launch Date is "Request Under Review." I don't understand why this works and the above doesn't!

=IF([Target Launch Date]@row = "Request Under Review", "Request Under Review", "On Track")

Paul Newcome

What about the [Days Until Due] column on those rows?

Julia Voyvodich

@Paul Newcome On those rows, Days Until Due has an #INVALID OPERATION error. This is because the dependent columns (Project Start Date and SLA Due Date) aren't auto-populated until Request Reviewed? is checked (like the 1st row in my screenshot).

The #INVALID OPERATION error doesn't seem to matter when I use the below test formula though. The below works as expected.

=IF([Target Launch Date]@row = "Request Under Review", "Request Under Review", "On Track")

That leads me to believe there's some issue with the Progress formula specifically. Could be a red herring though!

=IF(OR(Status@row = "Live", Status@row = "Complete"), "Done", IF([Days Until Due]@row < 1, "At Risk", IF([Target Launch Date]@row = "Request Under Review", "Request Under Review", "On Track")))

Paul Newcome

There's the issue. It works in the one standalone because you aren't referencing a cell with an error in it.

That error is pulling through though in the final formula because that formula is referencing the cell.

Whatever formula you have in that column, wrap it in an IFERROR.

=IFERROR(original_formula, "")

Julia Voyvodich

@Paul Newcome You're a genius!!! It worked! Thank you so much!

@Danielle Arteaga Thank you for helping me reinvent my workflow!

Danielle Arteaga

@Paul Newcome has helped me SO MANY times. :-) Glad I could contribute. Good luck with the new workflow!

Paul Newcome

Happy to help. 👍️