Forum Discussion

Riley2435's avatar
Riley2435
Copper Contributor
Oct 16, 2024

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. 

 

  • Riley2435 

    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.