Forum Discussion
Riley2435
Oct 16, 2024Copper Contributor
cond. format time if spec. value is pm, entered value AM next day
Hello,
I'm trying to get a spreadsheet to conditional format if an actual time is past the scheduled time. I have my formulas built in where the condition is reading when I input values. My issue is if I have a PM scheduled time entered, then the actual time is AM (of the next morning) then I need that to show as late. Is there a way to do this so the actual AM will register being as being greater than the scheduled PM so it will flag red for late. Any help, or even a different formula would be greatly appreciated.
These are my current conditions set
This is my data in military time, Row 4 is my example where 17:05 (5:05 pm) is my scheduled, the actual was late at 00:30 (12:30 am) next morning. I can't have date and time in the cell so I can't figure out how to do it.
Assuming that the delay will never be more than 12 hours:
=E2+(D2-E2>12/24)<=D2
and
=E2+(D2-E2>12/24)>D2
and similar for columns F and G.