Conditional Formating based on Time of the day

Karla Rodriguez

@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

Paul Newcome

Here is my suggestion:

Use this to convert the Time Request:

=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)

Then use the same formula to calculate the End as you are doing.

Then compare these two numbers.

Paul Newcome

Are you able to provide a link to the thread? I don't remember using the MOD function as you have listed in the [End Time] column, so it would be helpful if I could look back at that one to get the context and a little refresher on why I might have used that.

Karla Rodriguez

I'm sorry, my bad. The actual question where I got your formula was called Can you calculate time in Smartsheet?

Here is the link.

https://app.smartsheet.com/b/publish?EQBCT=d861f6ccf0e048b98906495ed74671fc&_ga=2.226165914.1409076172.1616386134-1986840033.1603219736&_gac=1.238274100.1615321995.EAIaIQobChMI0vK3q4ek7wIVwRd9Ch3myAD4EAAYASAAEgJiyfD_BwE

thank you for your time!

Paul Newcome

Here is my suggestion:

Use this to convert the Time Request:

=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)

Then use the same formula to calculate the End as you are doing.

Then compare these two numbers.

Karla Rodriguez

thanks again Paul, my file is working wonderfully.

you are the master!

Paul Newcome

Happy to help. 👍️