Conditional Formatting Digital Clock

I was staring at a video player the other day and thinking about digital clocks, specifically, of course, digital clocks created in Excel. As I imagined, there are quite a few out there: Juan Pablo Gonzalez already had one on DDOE back in 2004, Andy Pope’s is indistinguishable from the real thing, and Tushar Mehta’s is accurate to within one nanosecond every three years. But as far as I can tell there aren’t any created using conditional formatting. Let me know if I’m wrong, but meanwhile here’s a conditional formatting digital clock in a live workbook.

As the notes in the worksheet above say, you can update this clock by clicking in a cell and hitting F9. Not very convenient, but the best I can do without macros. (See the download at the end for full automation). You can adjust the time to your location, instead of that of your server, with the “Hour Offset” setting. To see the full works click on the second worksheet tab.

My basic idea was to use conditional formatting cell borders to form the digits. Each digit would consist of two cells on top of each other.

The first issue was how to have doubled lines for cell borders. Conditional formatting doesn’t have this option. Regular cell borders do though, so the answer was to put double line borders around all the digit cells and then have the conditional formatting “erase” the unneeded ones.

border settings table

The formatting feeds from this grid, which you can see on the second worksheet. Each clock digit is formed from two cells, the “top” and “bottom.” Each of the four borders for each cell has a 0 or 1 setting.

I originally had the table rows filled with 1, 2, 4 and 8 for each cell, thinking to roll the four numbers up into something like a composite enumerated value. Then I tried to use Mod to parse the rolled-up result for each half-digit. If that sounds confusing, it should! I eventually realized that since I essentially needed to determine if each border’s “bit” was on or off, I should just use a table with zeros and ones.

The conditional formatting formula, listed below, finds the relevant digit and it’s top or bottom position and checks whether it’s set to 0. If so, the conditional formatting “erases” the existing border. Otherwise the original double border is left in place.

There are actually four very similar formulas. The only thing that changes is the …”,1)=0″ part at the end. That’s checking the first column, i.e., the left border. The other formulas check, the 2nd, 3rd and 4th columns (the top, bottom and right borders).

=INDEX($R$6:$U$25,
SUMPRODUCT(
($P$6:$P$25=$S1)*
($Q$6:$Q$25=(MID($T$1,((COLUMNS($A:A)+1)/2),1)))*
(ROW($P$6:$P$25)-ROW($P$5)))
,1)
=0

It’s a two-dimensional Index formula. The most interesting thing is that the row dimension is determined by a Sumproduct formula, which finds the row that has the correct digit and top or bottom position. I’ll try to do a short post on this sometime, or if anybody has a good link, let me know.

clock's off button

This digital clock requires Excel 2007 or 2010 because there are four conditions per cell, and Excel 2003 only supports three. I could maybe figure a way around that, but… nah!

You can download the Excel 2007/10 .xlsm zip file, complete with “clocks on / clocks off” button.

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>