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.