Learn How to Highlight Dates and Times in Excel Using Conditional Formatting and Formulas

Conditional formatting is a feature in Excel that allows you to apply different formats to cells or ranges of cells based on certain conditions or rules. You can use conditional formatting to highlight, color, or change the font of cells that meet specific criteria, such as dates, times, values, formulas, or text.

One of the common uses of conditional formatting is to format dates and times based on various factors, such as weekdays, weekends, holidays, working hours, deadlines, or durations. For example, you may want to highlight cells that contain dates within a certain range, or cells that contain times between certain hours of the day and days of the week.

To conditionally format dates and times in Excel, you need to use formulas that can evaluate the date and time values in the cells and return TRUE or FALSE. Excel has several built-in functions that can help you with this task, such as:

• DATE(year,month,day): returns a date value based on the given year, month, and day arguments.
• TIME(hour,minute,second): returns a time value based on the given hour, minute, and second arguments.
• NOW(): returns the current date and time.
• TODAY(): returns the current date.
• WEEKDAY(date,[return_type]): returns a number that represents the day of the week for a given date, optionally based on a return type argument that specifies the starting day of the week and the numbering system.
• HOUR(time): returns the hour component of a time value, as a number from 0 to 23.
• MINUTE(time): returns the minute component of a time value, as a number from 0 to 59.
• SECOND(time): returns the second component of a time value, as a number from 0 to 59.
• TEXT(value,format_text): converts a value to text in a specified format, such as date or time.

By combining these functions with logical operators and other functions, you can create complex formulas that can check if a date or time value in a cell meets your desired criteria, and then apply a conditional format to that cell.

Procedures

To conditionally format dates and times in Excel, you need to follow these steps:

1. Select the cells or range of cells that you want to format.
2. Go to the Home tab, click the Conditional Formatting button, and choose New Rule.
3. In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
4. In the Format values where this formula is true box, enter the formula that can evaluate the date and time values in the cells and return TRUE or FALSE. You can use the functions and operators mentioned above, and refer to the cells using absolute or relative references as needed.
5. Click the Format button, and choose the format that you want to apply to the cells that meet the criteria. You can use the tabs for Number, Font, Border, and Fill to customize the format as you like.
6. Click OK to close the Format Cells dialog box, and then click OK again to close the New Formatting Rule dialog box. You should see the conditional format applied to the cells that match the formula.

Explanation

To illustrate how to conditionally format dates and times in Excel, let’s use an example scenario. Suppose you have a table that shows the start and end times of various tasks in a project, and you want to format the cells that contain times between 8 am and 5 pm on weekdays, as shown below:

To do this, you need to create a formula that can check if the time value in a cell is between 8 am and 5 pm, and if the date value in the same cell is a weekday. One possible formula is:

=AND(HOUR(B2)>=8,HOUR(B2)<=17,WEEKDAY(B2,2)<=5)

This formula uses the AND function to combine three logical conditions:

• HOUR(B2)>=8: checks if the hour component of the time value in cell B2 is greater than or equal to 8, which means 8 am or later.
• HOUR(B2)<=17: checks if the hour component of the time value in cell B2 is less than or equal to 17, which means 5 pm or earlier.
• WEEKDAY(B2,2)<=5: checks if the weekday number of the date value in cell B2 is less than or equal to 5, which means Monday to Friday. The second argument of the WEEKDAY function is 2, which means the week starts on Monday and ends on Sunday, and the weekdays are numbered from 1 to 7.

If all three conditions are TRUE, the formula returns TRUE, and the conditional format is applied to the cell. If any of the conditions is FALSE, the formula returns FALSE, and the conditional format is not applied to the cell.

To apply this formula to the cells in column B and C, you need to use relative references for the row numbers, and absolute references for the column letters. This way, the formula can adjust to each cell in the range, but still refer to the correct column. The formula becomes:

=AND(HOUR($B2)>=8,HOUR($B2)<=17,WEEKDAY($B2,2)<=5)

You can then use this formula in the New Formatting Rule dialog box, and choose a format for the cells, such as a green fill color. The result should look like this:

As you can see, the cells that contain times between 8 am and 5 pm on weekdays are highlighted in green, while the cells that contain times outside of this range or on weekends are not.

Other Approaches

There are other ways to conditionally format dates and times in Excel, depending on your specific needs and preferences. Here are some alternative approaches that you can try:

• Use the built-in rules for dates and times: Excel has some predefined rules for formatting dates and times, such as yesterday, today, tomorrow, last week, next week, last month, next month, and so on. You can access these rules from the Conditional Formatting button on the Home tab, and choose Highlight Cell Rules > A Date Occurring. These rules are easy to use, but they have some limitations, such as they only work for selected cells, and they are always based on the current date and time.
• Use the TEXT function to convert dates and times to text: Another way to conditionally format dates and times in Excel is to use the TEXT function to convert the date and time values to text in a specified format, and then compare the text with your criteria. For example, you can use the formula =TEXT(B2,"ddd")="Mon" to check if the date value in cell B2 is a Monday, and apply a format accordingly. The advantage of this method is that you can use any custom format for the date and time values, such as “yyyy-mm-dd”, “hh:mm:ss”, “mmm dd”, and so on. The disadvantage is that you need to use double quotes for the format and the criteria, which can be prone to errors and hard to read.