Those Pesky New Year Digits

Using conditional formatting to alert yourself of wrong year.

Now that it is a new year, sometimes we forget to date things with the correct year, but for me, it usually only happens a couple times before the new digits are embraced.

The brain freeze I have each year happens when I'm entering data in Excel. Much of the data I enter has a date that is from the  previous month. Since Excel defaults the year to the current year, unless something else is typed, I usually just type the month and day and let the computer do the rest. In January, data entry can problematic for me because it may not be immediately noticeable if I forgot to type the correct year in a December cell.

To make my life easier I use conditional formatting to highlight cells that have future dates. A red highlight alerts me that I have entered a bad year.

Follow these steps to set up Conditional Formatting:

1. Highlight the Excel column(s) you will be typing the dates in. (example 1)
2. Click the Conditional Formatting button in the toolbar. (example 2)
3. Pick Highlight Cell Rules.
4. Pick Greater Than.

Now you should have a dialog box, in the formula box type  =TODAY() as in the example 3. You can modify it with math if needed, in example 4 I added 30 days so a date in the next 30 days will not highlight with a color box.

Next set the highlight and font color to your choice and press OK, and you are done.

Now when you type a year greater than today,  the cell lights up with a light red background and red text. Give it a try.

Conditional formatting is handy for a variety of reasons that I will be covering in future posts. Feel free to post a memo to this entry and please subscribe to my blog to be notified for new posts.

I used an older version of Excel to screenshot these examples, what you see on your computer may vary a little. Y