# Conditional formatting based on the time element of timestamps

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

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.}

## 1 answer

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