Conditional Formating based on Time of the day

@Paul Newcome

Hey Paul, I was following some of your threads for Formulas with time, and I found one that has been really helpful. But there it seems to be something off.

I found your answer under the question "Calculating time worked for Employees". But the formula doesn't work well when you are adding minutes to a late AM hour, that will transform to PM. (line 2-5 from my example below).

I need to highlight the column '3PL PO to System", if the time elapsed is more than 30 minutes after "Time Request is recorded"

Time Request: Manual Entry

Max time: Manual Entry and standard to 30 min.


=IF(VALUE(LEFT([Time Request]@row, FIND(":", [Time Request]@row) - 1)) <> 12, IF(CONTAINS("p", [Time Request]@row), 12), IF(CONTAINS("a", [Time Request]@row), -12)) + VALUE(LEFT([Time Request]@row, FIND(":", [Time Request]@row) - 1)) + (VALUE(MID([Time Request]@row, FIND(":", [Time Request]@row) + 1, 2)) / 60) + ([max time elapsed]1 / 60)

End time:

=MOD(INT(End@row), 12) + ":" + IF((End@row - INT(End@row)) * 60 < 10, "0") + (End@row - INT(End@row)) * 60 + IF(End@row >= 12, "pm", "am")


=IF([3PL PO to System]@row = "", "", IF([3PL PO to System]@row >= ([End time]@row), 1, 0))

3PL PO to System: Manual Entry

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!