paper reverie

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. :)

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!