Conditional formatting based on time of day

This may be a little less clunky, as it requires a single 'extra' column, which is the one that gets highlighted.

Make a copy of your existing table, and work on the copy if you decide to try it.

Column letters in the description are fit to the data locations in the example, not to fixed locations. Adjust to match your document.

Column D contains the Date and Time list. I've left both the Date and the Time parts visible to show that the date does change (on line 28) but the highlighting is controlled by the time value.

Column E is there to increment the D&T value by 52 minutes per row. This is to ensure a change of date and more than one row with a time value in each quarter of the 24 hour day, and would not be a part of your table.

Column F contains a simple formula.

Entered in C2: TIMEVALUE(D2)

Then filled down to the last cell in the column (or the last row with data in column D).

The TIMEVALUE function returns a number representing the fraction of the day that has passed at the time of day expressed in hours, minutes and seconds in the time part of the D&T value in the same row of column D.

The four Conditional Highlighting rules act in the order they are listed.

Rule 1: If the timevalue in this cell is less than or equal to 1/4 (0.25), then apply red fill to the cell background.

Else: try the next rule.

Rules 2 and 4 (and originally, rule 3) are the same as Rule 1 with two exceptions:

They have a higher 'try the next rule' value than the previous rule, and

the fill colour they use is different from that used in Rule 1.

Rule 3 has the highlighting changed from 'fill the cell' to a 'custom format' that says 'fill the cell with green' AND colour the text 'green', effectively hiding the Time Value fraction in the cell. One difficulty with this is that the fill colours are named in the custom format menu, but the text colour settings are chosen from a 'colour patch' table, which may mean some of the named colours do not have an exact (or very close) match on the 'colour patch' table.

The steps below can be 'fiddley. You should probably make a new back-up copy of the page before starting.

One further variation that will make a 'colour fill' appear to be in the cells of Column D is to:

• Remove Column E from the table to become a new table on its own.
• Set its 'regular' fill colour and text colour to white.
• Set its Highlighting rules to a Custom format with the colour you want for the Fill colour for each quarter.
• Set the highlight colour for the text (Timevalue fraction) to match the fill colour.
• Adjust its width to match that of column D
• Slide the single column table under column D, making sure the boundaries match.

Any highlight colour in column E should now show through the transparent background of column D as 'background fill'.

Regards,

Barry

I think your four-column method is the most straightforward and is easy to expand if you want shorter/more time slots. But here is a janky way to highlight four time slots with the addition of only one column. I haven't tested it extensively but it seems to work.

Formula in C2 = IFS(TIMEVALUE(B2)<0.25,B2,TIMEVALUE(B2)<0.5,B2+DURATION(1) ,TIMEVALUE(B2)<0.75,B2−DURATION(1),TRUE,"")

Fill down to complete the column. You'll be hiding the column so no need to worry about the error triangles

If less than 6AM then it puts the same time, else if less than 12PM then it puts the time + 1 week, else if less than 6PM then the time - 1 week, else it puts "".

The highlighting rules are number rules, not date rules. Date rules should work for at least the first three numeric rules if you want to try them. The "not equal to" rule might be better if it was a "text is not equal to" rule rather than numeric. The "cell is blank" rule at the top is so blank cells do not get any of the colors.

I don't know. It figures it out somehow.