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.

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.

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

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

dsmartsheet36

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.