This article will give you a complete overview of how Excel Change color if time is in range.
Changing cell colors based on time ranges in Excel offers facilitates quick analysis. It enables users to manage schedules effectively, track time-related data, identify conflicts or gaps, and make informed decisions.
By leveraging this feature, individuals and organizations can improve efficiency and gain valuable insights from their data.
By employing conditional formatting, a built-in feature in Excel, users can create rules that automatically change the background color of cells based on specified criteria.
Today we will discuss several ways to apply conditional formatting to change color based on both time and date in Excel.
Excel Change Color If Time Is in Range: 3 Suitable Ways
Conditional formatting in Excel is a feature that allows you to apply formatting to cells based on specific conditions or rules.
Excel provides a range of predefined formatting options for conditional formatting, or you can create custom rules using formula.
This section describes 3 ways in which Excel changes color if time is in range.
1. Formatting Cells Between Time Range to Change Color
We have a dataset of some students submitting their assignments on different timestamps. The range of the submission time is also included. In the Conditional Formatting feature, there is an option to format only cells that contain specific cell values between the range. Here, we need to change the color of cells that are in the range. Let’s check how Excel change color if time is in range!
- Select the range of cells > go to the Home tab > click Conditional Formatting > select New Rule.
- The New Formatting Rule dialog box will appear.
- Here, choose Format only cells that contain from the Select a Rule Type field.
- In, our dataset, cell E5 contains the starting range and cell F5 contains the ending range.
- So, in the Format only cells with section, enter your starting and ending range.
- Click Format feature to choose a color to format cells.
- In the appeared Format Cells dialog box, go to the Fill section > choose a color > click OK.
- Close the New Formatting Rule dialog box by clicking OK.
- Hence the selected cells whose values are in the specified range will change their color.
2. Use Formula in Conditional Formatting to Change Color Based on Time
For the same dataset, you can apply the Excel formula in Conditional Formatting.
- Select the range of cells and again launch the New Formatting Rule dialog box.
- Here, choose Use a formula to determine which cells to format from the Select a Rule Type field.
- Now apply the formula in the “Format values where this formula is true” field and choose a color. We have used the AND function in the formula as we have to match our values between two values.
=AND(C5>=$E$6,C%<=$F$6
- As a result, Excel will change the cell color.
3. Use Formula in Conditional Formatting to Change Color Based on Date and Time
Sometimes, your dataset may contain time along with the date. For example, the NOW function returns both date and time. So, if your dataset has different starting values describing both time and date and the ending range is the value returned by the NOW function, then the formatting formula can be as below:
=AND(C5>=$E$6,C5<=NOW())This formula finds whether cell C5 has a value between the value of cell E6 and the value returned by the NOW function.
And you will get the cell color changed.
How to Change Color Based on Date in Excel
In certain cases, your dataset may contain dates and you will need to apply conditional formatting based on date in Excel.
1. Apply the “Date Occurring” Option of Conditional Formatting for Dates
In the Conditional Formatting feature, there is a built-in option “A Date Occurring” that is applied for date only. To apply this option:
- Select the range containing dates > go to the Home tab > click Conditional Formatting > select Highlight Cells Rules > click A Date Occurring option.
- Now, A Date Occurring dialog box will appear.
- Here, you will find several options for dates from which anything you can choose (i.e. Last Month). Based on your choice, this command will change the cell color. As we have chosen Last Month, Excel will change the dates of the last month.
2. Change the Color of Cells for Weekends
If you want to change the color of the weekends from the date list of your dataset. You can use the WEEKDAY function as the formula.
- Select the range of dates.
- Launch the New Formatting Rule dialog box.
- Apply the following formula:
=WEEKDAY($B5,1)>5💡 Formula Explanation
In this formula, the return type is set to 1, which means the weekdays are numbered from 1 (Sunday) to 7 (Saturday).
And The greater than operator “>” is used to compare the weekday number obtained from the “WEEKDAY” function with the value 5. If the weekday number is greater than 5 (i.e. if it represents a Saturday or Sunday), the formula will return “TRUE”; otherwise, it will return “FALSE.”
- Hence Excel will change the cell color on the weekends.
3. Highlight Holidays in Excel
Besides the weekends, you can also highlight some specific weekends.
We have specified some weekends in the dataset: 6/4/2023, 6/13/2023, 6/20/2023.
On the previously weekend highlighted sheet, now apply the MATCH function to highlight the holidays:
=MATCH($B5,$H$4:$H$6,0)This formula will match the dates with the holidays specified in the range $H$4:$H$6.
The dates which match with the specified holiday will now change their color.
4. Change Cell Color Based on a Specific Date
If you want to change the cell color of your date list based on a specific date, you can apply the DATEVALUE function in conditional formatting. The DATEVALUE function converts a date in the form of text to a number that represents the date in the date-time number codes.
The syntax of the DATEVALUE function is:
=DATEVALUE(date_text)Here, the argument date_text is: date in text format.
Apply the following formula in conditional formatting:
=$D5=DATEVALUE("7/22/2023")This formula finds the date value of “7/22/2023” in column D and highlights the cell values that match with “7/22/2023”.
5. Change the Cell Color of Dates Based on Today
To highlight the cell containing the date of today, apply the TODAY function in the conditional formula. The TODAY function returns the current date formatted as the date.
=$C5=TODAY()
6. Highlight Dates Based on Multiple Criteria
When you need to change the cell color and highlight dates by applying conditional formatting based on multiple criteria, you can use the IF function in conditional formatting.
We have now a dataset of new and repeated customers and their order dates.
If you want to highlight the whole rows where both the “Repeat” customers and order date is greater than today (7/19/2023):
- Select the full range of datasets and apply the following formula in the conditional formatting:
=IF($C5>=TODAY(),IF($D5="Repeat", 1, 0), 0)
7. Highlight Upcoming and Previous Dates
Let’s say the TODAY function’s return date of today is 7/16/2023.
If you want to highlight 10 upcoming days from today’s date, apply the following formula in the conditional formatting.
=AND($C5-TODAY()>=0,$C5-TODAY()<=10)
If you want to highlight 10 previous days from today’s date, apply the following formula in the conditional formatting.
=AND(TODAY()-$C5>=0,TODAY()-$C5<=10)
Frequently Asked Questions
1. Can I apply conditional formatting to highlight time ranges in Excel for both single cells and cell ranges?
Ans: Yes, you can apply conditional formatting to highlight time ranges in Excel for both single cells and cell ranges.
2. Is it possible to create dynamic time ranges for conditional formatting in Excel, such as based on a changing reference cell?
Ans: Yes, by using formulas and cell references within the conditional formatting rules, you can dynamically adjust the time ranges based on the value of a reference cell.
3. How can I remove or clear conditional formatting rules that have been applied to time-based cells in Excel?
Ans: To remove conditional formatting rules from time-based cells in Excel, select the cells, go to the Home tab > click Conditional Formatting > and choose Clear Rules from the drop-down menu.
Key Takeaways from the Article
- Conditional formatting in Excel allows you to change the color of cells based on specific time ranges.
- Conditional formatting is a dynamic feature in Excel. So, if the time values change, the cell colors will automatically update to reflect the new conditions.
- AND function is particularly useful when setting up conditional formatting for time ranges.
- Conditional formatting is not limited to time ranges. You can also apply it for a date range.
Download Practice Workbook
You can download the practice workbook from the link below.
Conclusion
So, we can say that conditional formatting is greatly applicable in Excel to change color if time is in range. Throughout the article, we have shown different conditional formatting options and formulas to change color based on both time and date.
Customizing the color scheme used for conditional formatting can make it easier to interpret data at a glance.
Applying conditional formatting for time ranges in various scenarios such as tracking project deadlines, monitoring employee work schedules, or managing time-sensitive tasks can be your lifesaver.
