Formula to show Status based on several fields that may or may not have a date entered.

Jeana Rogers · 02/10/20

Hi, first let me say that Smartsheet formula's are fairly new to me so if I'm approaching this all wrong please tell me and I'd appreciate any suggestions.

I'm trying to establish a formula for a Text field that will automatically populate with the appropriate status based on whether several other fields have a date in them or not.

Here's the fields I'm working with:

Current status - this will have the formula

Uploaded - if date entered Current status should display "Uploaded to Vimeo"

Captions and transcripts requested - if date entered Current status should display "Captions and transcripts requested"

Captions and transcripts to Copyeditor - if date entered Current status should display "To Copyediting"

Captions and transcripts Complete - if date entered Current status should display "Captions and transcripts complete"

Final links sent to requester - if date entered status should read "Job Completed"

Thank you in advance for any help you can offer.

Jeana

Genevieve P. · 02/12/20

Hi Jeana,

Since logic formulas read left-to-right and stop whenever their first criteria is met, you’ll actually want to work backwards in order. Start with the Final task and then go through each rule.

For example, first Rule:

Final links sent to requester - if date entered, status: “Job Completed"

=IF(ISDATE([Final links sent to requestor]@row), “Job Completed”

You’ll use the same structure for each rule, just with a different column [in these] and a different instruction “in these”.

Rule 1

=IF(ISDATE([Final links sent to requestor]@row), "Job Completed",

Rule2

IF(ISDATE([Captions and transcripts Complete]@row), "Copy and transcripts complete",

Rule 3

IF(ISDATE([Captions and transcripts to Copyeditor]@row), "To Copyediting",

Rule 4

IF(ISDATE([Captions and transcripts requested]@row), "Captions and transcripts requested",

Rule 5

IF(ISDATE(Uploaded@row), "Uploaded to Vimeo"

Keep in mind that this won’t take into account if any of the previous cells are blank when they shouldn’t be - it will just look through the columns which one is first filled in from this order:

[Final links sent to requestor]

[Captions and transcripts Complete]

[Captions and transcripts to Copyeditor]

[Captions and transcripts requested]

Uploaded

Final Formula:

=IF(ISDATE([Final links sent to requestor]@row), "Job Completed", IF(ISDATE([Captions and transcripts Complete]@row), "Copy and transcripts complete", IF(ISDATE([Captions and transcripts to Copyeditor]@row), "To Copyediting", IF(ISDATE([Captions and transcripts requested]@row), "Captions and transcripts requested", IF(ISDATE(Uploaded@row), "Uploaded to Vimeo")))))

Here are some Help Center articles I used to build this: IF Function / ISDATE Function / @row Function

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P. · 02/12/20