Using conditional formatting to alert yourself of wrong year.
Now that it is January 2018, sometimes we forget to date things with the correct year, but it usually only happens a couple times before the new digits are embraced.
The brain freeze I have every 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 assumes the year is 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. Give it a try.
Follow these steps to set up Conditional Formatting:
- Highlight the Excel column(s) you will be typing the dates in. (example 1)
- Click the Conditional Formatting button in the toolbar. (example 2)
- Pick Highlight Cell Rules.
- 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.
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. You can click each example for a larger view.