Formula Help - Open, Pending, At-Risk and Complete

Adriane Price

Hello,

I am looking to create a formula if a request is/was submitted I would like it to display open, pending, at-risk, or complete. I also need to exclude changes to that request if the status reflects canceled, rescheduled, no-show, or declined.

Currently, I have a vlookup to display only the open or completed requests.

I am looking to measure the following, if feasible or if there are other suggestions then I am all ears.

If date evaluated is not today or in the future = open

If date evaluated and today are not equal or match = pending

If date evaluated is in the past and it has been more than 1 day since it has been submitted = at risk

If Date Received is not empty = complete

AMER Tech Screen Results scrubbed.xlsx

TS status helper column drop-down list:

Canceled - Candidate

Canceled - Tech Screener

Decline - Candidate

Decline - Tech Screener

No show - Candidate

No show - Tech Screener

Rescheduled - Candidate

Rescheduled - Tech Screener

I still need to use the TS Complete column as a checkbox and the Tech Screen progress column.

My thought was I could create another column to indicate the open, pending, at-risk, or completed progress of the request.

How do I create this formula (assuming I should use a formula) to show the status of the request?

Mike TV

@Adriane Price

I started to work on a solution for you but then realized that your criteria are not all defined properly for someone to set this up for you. This part doesn't make sense to me "If date evaluated and today are not equal or match = pending". Writing a criteria for not equal to today will conflict with any other criteria you try to set up. How specifically do you want to exclude statuses of cancelled, rescheduled, etc? What should happen if they're excluded? Cancelled is misspelled.

It's been awhile since you posted this, so maybe you've already figured out a solution or a work around?

Mike TV

@Adriane Price

I started to work on a solution for you but then realized that your criteria are not all defined properly for someone to set this up for you. This part doesn't make sense to me "If date evaluated and today are not equal or match = pending". Writing a criteria for not equal to today will conflict with any other criteria you try to set up. How specifically do you want to exclude statuses of cancelled, rescheduled, etc? What should happen if they're excluded? Cancelled is misspelled.

It's been awhile since you posted this, so maybe you've already figured out a solution or a work around?

Adriane Price

@Mike TV - so instead of using a formula, I ended up creating helper columns with date stamps. This way It shows the status, when it occurred and how many days. The time I asked the question until approximately 2 weeks later management redirected what they were looking for. This is what I ended up with.

Thank you for taking the time to look at my mess!