This is rapidly becoming a blog of all sorts, from a craft journal to one that has everything short of daily ramblings about the mental-emotional typhoons and real-life drama also known as My Life. Sorry, THAT blog is private.

Anyway..

Two days ago, my sub-supervisor (SSP) came to me asking if I knew how to create alerts in MS Excel for when a certain date is reached. This is to serve as a reminder for when calibration of equipment needs to be carried out (every 6 months).

I've been an Excel user for years, but sad to say, I never knew such things could be done. In the past, I've only needed to calculate scientific data and plot graphs/charts, so I've got that downpat. But to create alerts?

This had to be about cell formatting of some sort, so clicking on Format in the Toolbar, I found this previously unnoticed option called "Conditional Formatting". The title itself was obviously the solution to our puzzle. Bingo!

But wait a sec. It's not that straightforward. While it's easy peasy if you, for instance, wanted to highlight cells that contained a specific, static value, it's another story altogether to do the same with constantly-changing dates/times.

I played around with the TODAY() and NOW() functions for a good hour or so. But experimenting with dates meant that I had to wait till the next day to see if any effect took place! I was also too blinded by enthusiasm to use backdating. *slaps forehead*

Anyway, I thought of another way to test results faster, which was to use Time. This meant that my objective would now be to get a cell highlighted in, say, one minute from the current time.

Trying to achieve that took me another working day, and proved futile. Online forums and Help sites didn’t have what I wanted either. No choice but to seek one-to-one professional help – from Chandoo (a.k.a. Pointy Haired Dilbert), “an engineer / manager, currently working with India's largest IT services firm as a consultant”.

After a couple of to-and-fros, he found me a solution. Capital! (Been reading too much Tolstoy…am becoming fond of that word.)

Testing it out right away and to my delight, finding it successful, I proceeded to write a much simplified step-by-step procedure. It was meant for my SSP, in case he asked and I was too lazy to explain, but in case anyone’s interested, I’d put it up here too. For those already savvy and find this topic trivial, click the X at the top right of your browser. :)


STEP-BY-STEP TUTORIAL FOR MS EXCEL CONDITIONAL FORMATTING

Objectives:

a) Alert when a targeted date is reached.

b) Alert when a targeted time is reached.

c) Unshade cell when info has been keyed in.

a) Alert when a targeted date is reached.

Let cells A1 and A2 be used in this example.

  1. In A1 – key in formula: =TODAY() .
  2. In A2 – key in targeted date in this format: m/d/yyyy
    (if you want a different date format, make sure you change for both A1 and A2.)
  3. Selecting cell A1, go to Format > Conditional Formatting.
  4. Select “Formula is”, then type: =A1=A2 .
  5. Click “Format” and choose desired alert method.
  6. Click OK.

b) Alert when a targeted time is reached.

Let cells B1 and B2 be used in this example.

  1. In B1 – key in formula: =TIME(HOUR(NOW()), MINUTE(NOW()), 0) .
  2. In B2 – key in targeted time in this format: 10:00 AM.
    (if you want a different time format, make sure you change for both B1 and B2)
  3. Selecting cell B1, go to Format > Conditional Formatting.
  4. Select “Formula is”, then type: =B1=B2 .
  5. Click “Format” and choose desired alert method.
  6. Click OK.

Whenever you want to see the spreadsheet recalculating to the latest date/time, press F9.

(Keeping the spreadsheet active by keying in info or simply typing randomly in other cells will also refresh the date/time but why do that if you can just press a button, right?)

c) Unshade a cell when info has been keyed in.

This is useful for spreadsheets that are used by more than one user. There may be blanks that are meant for another colleague to fill in. Usually, we highlight the blank cells for others to see, but it’s troublesome to have to remove the shading when the task is completed.

  1. Select cell or range of cells to be formatted. In this example, it is assumed that a range of cells, C1:C100 is selected.
  2. Go to Format > Conditional Formatting
  3. Select “Formula is” then key in formula: =ISBLANK(C1) .
  4. Click “Format” then in the Pattern tab, choose a color for the selected blank cells.
  5. Click “OK”.

The formatting will be applied to the whole range of cells selected in step 1.


As far as I know, all the methods described works for both Excel 2003 and Excel 2007.

Once I understood the concept, I found a bunch of other ways to create time and date alerts. It feels pretty great when you find your own way about things and they actually work.

It’s a very useful tool, Conditional Formatting. You can track changes, check for errors, see only the info you want without going through them one by one like with Ctrl+F, yadda yadda yadda.. Just Google “Excel Conditional Formatting” and you’ll know.


I’m sure gonna be making use of it often from now on!


4 comments:

Steve said...

Okay, I have another variation to ask :-)

What do you add to the =A1=A2 example if you want it to be red coloured after the reached date....
Eg: The date is 21/8/09... the target date WAS the 21/08/09 but you look at the spreadsheet on the 24/08/09. Your formula only shows RED on the 21/08/09. Then returns to Black AFTER the 21/08/09.

With your answer, my dilema is solved with thanks.

Steve

illuminette said...

Hi Steve,
That's a good question. After completing steps 1 to 3 in Part a, try the following:

Get these options selected:

Cell value is | Greater than or equal to | =A2 (or whichever the cell of your Targetted Date)

Then continue to choose the alert highlight of your choice.

Hope this helps! :)

Adam said...

I know this blog entry is years old, but thanks, it just saved me a lot of hair pulling!

I wanted to have my worksheet auto-update the Now() values every few minutes so that when Now() reached the value in the cell, it automatically changed formatting to alert me that a time threshold had been reached. I used your instructions here and then added a macro to do that (also discovered on the internet!).

In case that helps anyone else, I pilfered the macro to do that from here: http://www.mrexcel.com/archive/VBA/4217.html

Thank you :)

illuminette said...

Hello Adam, it's an awesome feeling to hear that my posts are still relevant 4 years on. Glad you got what you needed and brought it to a "macro" level! ;)