Notifications
Sign Up Sign In
Q&A

Conditional formatting based on the time element of timestamps

+0
−0

There is presently an unanswered Q on Super User, tagged [microsoft-excel] [macos-catalina], that I interpret as, for data such as below:

Data sample and location

07-15 11:32 | 07-16 03:26 | 07-16 08:34 | 07-16 22:32

and formulae in B4:B7 of:

=TEXT(TIMEVALUE(TEXT(A4,"dd-mm hh:mm")),"hh:mm")

(copied down from B4)

How to conditionally format times in ColumnB before 08:30 in green and after 18:00 in pink?

† Has been answered there since I started writing this.

Why should this post be closed?

0 comments

1 answer

+0
−0

First off, the formula in B4 is unnecessarily cumbersome. This works:

=MOD(A4,1)

The values in ColumnA are Text but in a formula Excel can automatically read them as date and time (Sheets is not as amenable). For this Excel treats date and time as a single number, with the whole number part the date and the fractional part the time. Where Excel interprets the content of A4 as a timestamp the time can be extracted (with MOD) by dividing the number by 1 and keeping only the remainder.

Conditional Formatting treats empty cells as of value 0 (less than 00:01) so rather than have blank cells colour filled, I suggest an additional condition.

Select ColumnB, clear any existing CF rules and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=AND(NOT(ISBLANK(B1)),MOD(B1,1)>0.75)

For before 08:30 and green instead:

=AND(MOD(B1,1)<8.5/24,NOT(ISBLANK(B1)))

In each case continue with Format..., select appropriate fill and OK, OK.

The equivalent formulae could be applied to ColumnA by extending the Applies to range. ColumnB might then be considered redundant. Written for Windows Excel but on macOS should be the same or very similar.

0 comments

Sign up to answer this question »