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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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 :)
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 282 Events
- 32 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!