Time-based rules don’t necessarily have to be hard-coded into conditional formatting options.
Conditional formatting can also be applied to time intervals or times that fall within a certain range.
Two-time values can be simply subtracted to get a time interval
Excel automatically recognizes values such as “9:14 AM” as valid time values.
This time, a darker red fill will be used.įigure 7. 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). It is now much easier to identify at a quick glance, which employees came later and/or left the workplace early. Conditional formatting applied to In and Out times Similarly, we will apply a “LESS THAN” rule for the Out times, and set the comparator value to 6:00 PM. Conditional formatting for employees that came to work late We will apply the following conditional formatting to the first column.įigure 5. 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). The basic dataset contains a list of employees with their starting and leaving times, along with a time interval representing total time worked. Let’s take the example of an attendance log for employees at a company. Formatting rules and standards Example: Employee attendance For example, time values that are greater or lesser than a defined value can be highlighted in red.įigure 3. Note that Conditional Formatting enables users to highlight cells based on their values.
Click on the Conditional Formatting option found under the Home tab in Excelįigure 2.
Select the cell or range containing time values.
To apply conditional formatting based on time values, follow the steps below. Final result Steps to apply Conditional Formatting in ExcelĬonditional formatting can be applied to individual cells or an entire data range. In this article, we will learn how to apply conditional formatting for time values in Excel.įigure 1. Using Conditional Formatting Times in ExcelĮxcel provides conditional formatting features for cells or ranges, using which, rule-based formatting standards can be applied easily.
0 kommentar(er)
