0

Those Pesky New Digits of the New Year

Posted by TetonTrekker on January 6, 2018 in Excel, Tips for the common user |

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.

Example 1

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.

Example 2

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.

Example 3

Example 4

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



Example 5

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.

Tags: , , ,

Post a Memo

Copyright © 2013-2018 Julie: Girl Friday All rights reserved.
This site is using the Desk Mess Mirrored Child Child-Theme, v2.5.1511966596, on top of
the Parent-Theme Desk Mess Mirrored, v2.5, from BuyNowShop.com