@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.
End:
=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")
F1:
=IF([3PL PO to System]@row = "", "", IF([3PL PO to System]@row >= ([End time]@row), 1, 0))
3PL PO to System: Manual Entry
