Tom’s Tutorials For Excel: Conditionally Formatting Early, Late, and Acceptable Times
Here is how you can use Conditional Formatting to identify times that are more than, or less than, a margin of acceptability. In the picture, the cells holding airline flight arrival times that occur more than 60 seconds earlier than their scheduled arrival time are shaded yellow. The Conditional Formatting formula in cell B12 copied down to cell B22 for the yellow cells is
=B12 < A12-(1/1440).
Cells holding an actual arrival time more than 1 minute later than their scheduled time are shaded blue. Their Conditional Formatting formula in cell B12 copied down to cell B22 for the blue cells is
=B12 > A12+(1/1440).
this is not working
Yes it is working and it does work as you can see by the picture. “This is not working” tells me and everyone reading this nothing useful about what exactly you are working with and what you are doing wrong.
This is useful – thank you. Can you show how I would so this for, a scheduled time in column A and highlight late arrivals of 15 minutes or more for dates over a week, say columns B-G
Not sure how your workbook is set up for that. If you are assessing flight times in 15 minute increments over a week’s time, I do not know how you would get a lot of value from that, leaving such a big gap of opportunities for 15-minute delays to anticipate so far out.