VideoPhoto

24 April, 2021

Conditional formatting, about Time

Let's assume, we have in a worksheet a range of Start and Stop times formatted as "hh:mm:ss", and we want to format somehow the cells, so that the times falling outside some time frame, e.g. outside 6:00 AM and 3:00 PM, are clearly highlighted.

To do that we need to get Values  of that two Times, using formulas like these:

=VALUE("06:00:00") resulting in 0.25, and =VALUE("15:00:00") resulting in 0.625 .

Knowing these values, we now use Conditional Formatting feature of Excel to format the outlying Times:
  • First, select your range of cells with times entered.
  • In the ribbon, select Conditional Formatting>Highlight Cells Rules. More Rules...
  • In displayed window select options and enter limiting numbers as shown in the figure below:

  • Then select formatting options you want to, e.g. Bold font or/and blue color, outline border, cell background color.

Example of formatting result is shown here (just random times entered) :


 Happy conditioning! 😁


 


No comments:

Post a Comment

All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.