IF AND Statement HELP
Looking for some help with am IF AND formula as I am pretty new to SS and stumped.
We use Smartsheet for time tracking and have a pretty complex solution. We are basically implementing a system where IF a user did not submit time the previous week, and the current day of the week is Thursday, then check a box in another column. Few notes.
- Each project has a Time Tracker sheet
- We have a "Date Helper" column
- The resource must be populated
- No Hours Worked must NOT be checked
- Status column will show if a user SUBMITTED time(Status are: Submitted, Approved, In Progress, Invoiced)
It is basically... IF RESOURCE IS NOT BLANK, AND NO HOURS WORKED IS NOT CHECKED, AND WEEKLY TOTAL HOURS = 0, AND DATE IS CURRENT WEEK THURSDAY THEN TRUE, ELSE FALSE.
Also can read as. IF CURRENT WEEK DAY IS THURSDAY, AND RESOURCE IS NOT BLANK, AND NO HOURS WORKED IS NOT CHECKED, AND WEEKLY TOTAL HOURS = 0, THEN TRUE, ELSE FALSE
Another option I have been considering is that we have a STATUS column. IF that status is not SUBMITTED, APPROVED or INVOICED, AND RESOURCE IS NOT BLANK, AND CURRENT WEEKDAY IS THURSDAY then TRUE, FALSE.
Answers
-
Hey @dsmartsheet36
We can build either of these formulas, depending on what you'd prefer. You could also create a Report that filters the sheet based on your criteria to review these rows as well.
Let's start with your first formula:
IF RESOURCE IS NOT BLANK, AND NO HOURS WORKED IS NOT CHECKED, AND WEEKLY TOTAL HOURS = 0, AND DATE IS CURRENT WEEK THURSDAY THEN TRUE, ELSE FALSE.
Try
=IF(AND(Resource@row <> "", [No Hours Worked]@row = 0, [Weekly Total Hours]@row = 0, WEEKDAY(TODAY()) = 5), 1, 0)
Note that you'll need to swap out the column names if any of them are different than what I have above. Then you can turn that into a column formula! Keep in mind that today is Monday, so they will all appear blank.. and that once Friday comes around these will all be un-checked again as well.
If you want to statically check the box, you could set up a Change Cell Workflow instead, one that runs every Thursday like so:
See: Change the Value of a Cell in an Automated Workflow and Create a Time-Based Automated Workflow
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
I think I like the automation workflow much better. The goal is to check the box "Missed Time Entry" once that criteria is met and I feel this does the trick. Going to do some testing and ensure it works properly.
Thank you! I will post back with the results as well.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 202 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!