Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

IF(AND With Time

Hello Brilliant People!

I'm stuck on this IF formula incorporating time and multiple conditions:

*Quick detail, I work in construction and this is regarding a form my guys use twice a day to do a safety walk of the job site. Both safety walks have a cut off time determining task compliance, and there are multiple job sites. AM needs to be in before 10:30AM and PM before 2:30PM.

So what I want the formula to consider is this:

Column [PM Score]: If the time is less than 2:30PM and [IF PM] = "PM", then value = "Pass: PM". But, if the time is greater than 2:30PM and [IF PM] is "PM", then "Fail: PM" or if one condition doesn't match (ie [IF PM] = AM rendering the need for score mute) then leave the value as blank.

The only reason why I incorporated the [IF PM] column is so that the formula doesn't mistake a reeeeeeaally late AM entry as a PM entry (the column uses 12:30 as the delimiter). Also, I don't want to consider a formula that identifies whether the same project submits twice in one day because not all projects require both an AM and PM check.

So therein lies my pickle. As you can see above, I'm not getting an error but it is returning the wrong value. Clearly the submitted time is after 2:30PM but it is returning "Pass: PM".

=IF(AND(Time@row > TIME(14, 30, 0), [IF PM]@row = "PM"), "Fail: PM", IF(AND(Time@row < TIME(14, 30, 0), [IF PM]@row = "PM"), "Pass: PM", " "))

Please help,

Thank you thank you! :)

Answers

  • Community Champion

    How is your "time" column formatted, and how is it populated?

  • Created timestamp from form submission → Time 12 Column → Time (24 Format)

  • Community Champion

    Okay, so looking at your screenshot, I am guessing that your [Time] and [Time 12] columns are formatted as duration? Here is how I would set them up:

    [Time] (Set as Duration)
    =TIME(RIGHT(Created@row, 8), 1)

    [Time 12] (Set as Duration)
    =TIME(RIGHT(Created@row, 8), 0)

    I believe the formula you posted above would then work correctly. I suspect your time columns are currently being treated as text.

    Also, it is possible to set column formulas for Duration columns by temporarily setting them to Text/Number, converting to column formulas, then setting back to Duration.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions