Formula to show Status based on several fields that may or may not have a date entered.
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
Best Answer
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks Genevieve,
This certainly works when I'm just looking for a date in the cell. I was not aware of the ISDATE funtion.
Thanks!
Jeana
-
Great! Glad this will work for you :)
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!