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
-
How is your "time" column formatted, and how is it populated?
-
Created timestamp from form submission → Time 12 Column → Time (24 Format)
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!