# Conditional Formating based on Time of the day

Options

@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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.

• Options

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

• ✭✭✭✭✭✭
Options

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.

• Options

thanks again Paul, my file is working wonderfully.

you are the master!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!