Conditional Formating based on Time of the day
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
Best Answer
-
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.
Answers
-
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.
-
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.
thank you for your time!
-
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.
-
thanks again Paul, my file is working wonderfully.
you are the master!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!