Go Back

Using Conditional Formatting Times in Excel

Excel provides conditional formatting features for cells or ranges, using which, rule-based formatting standards can be applied easily. In this article, we will learn how to apply conditional formatting for time values in Excel.

Figure 1. Final result

Steps to apply Conditional Formatting in Excel

Conditional formatting can be applied to individual cells or an entire data range. To apply conditional formatting based on time values, follow the steps below.

  • Select the cell or range containing time values
  • Click on the Conditional Formatting option found under the Home tab in Excel

Figure 2. Conditional Formatting feature in Excel

  • Select an appropriate formatting rule

Note that Conditional Formatting enables users to highlight cells based on their values. For example, time values that are greater or lesser than a defined value can be highlighted in red.

  • Apply the desired formatting

Figure 3. Formatting rules and standards

Example: Employee attendance

Let’s take the example of an attendance log for employees at a company. The basic dataset contains a list of employees with their starting and leaving times, along with a time interval representing total time worked.

Figure 4. Employee attendance records

Now, we will apply conditional formatting to each column to highlight employees that came to work late (after 9 AM) or left work earlier than their shift ended (before 6 PM). We will apply the following conditional formatting to the first column.

Figure 5. Conditional formatting for employees that came to work late

Similarly, we will apply a “LESS THAN” rule for the Out times, and set the comparator value to 6:00 PM. Here is the end result:

Figure 6. Conditional formatting applied to In and Out times

It is now much easier to identify at a quick glance, which employees came later and/or left the workplace early. We can take this a step further, and apply a custom formatting to the third column, where we will compare time interval value to be less than 9:00 (9 hours). This time, a darker red fill will be used.

Figure 7. Custom formatting applied

Notes

  • Excel automatically recognizes values such as “9:14 AM” as valid time values. Two-time values can be simply subtracted to get a time interval
  • Conditional formatting can also be applied to time intervals or times that fall within a certain range
  • Time-based rules don’t necessarily have to be hard-coded into conditional formatting options. You can use a reference to a cell containing a time value, rather than providing a value directly to the formatter

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

i need a conditional formatting in excel
Solved by X. B. in 17 mins
I need to use conditional formatting to highlight names that are displayed more then 2 times
Solved by M. Q. in 17 mins
I need help with conditional formatting in Excel.
Solved by T. S. in 15 mins

Leave a Comment

avatar