Ensure cleaner input dates with conditional formatting [quick tip]

Posted on May 12th, 2015 in Excel Howtos - 28 comments

Here is a familiar problem:

You create a workbook to track some data. You ask your staff to fill up the data. Almost all the input data is fine, except the date column. Every one types dates in their own format.

Here is a fun, simple & powerful way to warn your users when they enter wrong dates.

Use conditional formatting

Here is a quick demo:

cleaner-dates-with-excel-conditional-formatting-demo

cleaner-dates-with-conditional-formatting-setting up the rulesHow to ensure cleaner dates with conditional formatting?

  1. Let’s say your users need to enter date input in cell C3.
  2. Select C3 and go to Home > Conditional Formatting > New Rule
  3. Select the rule type as “Use a formula…”
  4. Use a formula like this: =ISERROR(DAY($C$3))
  5. Set up formatting to highlight incorrect dates
  6. Done

Explanation for the formula:

We will assume that C3 contains a valid date and try to do some sort of date operation on it, like finding the day of month using DAY($C$3). If this returns an error, that means we have an invalid date.

So to check the error status, we use ISERROR().

A caveat: dates are numbers

Since Excel dates are numbers, technically, your users can type a number in C3 (like 7) and our conditional formatting won’t trigger the error.

Bonus tip: Adding conditional formatting to an entire column

To add conditional formatting to an entire column,

  1. Let’s say the date inputs go to column C, starting with C3 as first input
  2. Select all the cells in column C that will have dates
  3. Set up the conditional formatting with below rule:
  4. =ISERROR(DAY($C3))

Download example workbook

Click here to download a simple example workbook with this technique. Examine the conditional formatting in C3 & D3 to learn more.

Do you use CF to nudge your users in right direction?

Excel Conditional Formatting is one of my all time favourite features. I use liberal amounts of CF on all my Excel recipes. I think CF makes a great ingredient if you are collecting user inputs.

What about you? Do you use conditional formatting to make sure your inputs are clean? What techniques do you use? Please share your thoughts and tips in the comments area.

Here are few more awesome ways to use conditional formatting:

28 Responses to “Ensure cleaner input dates with conditional formatting [quick tip]”

  1. ESG says:

    Love this site. Thank you!
    But is 3/5/2000 Mar 5th or May 3rd?
    Conditional formatting (and data validation) will not help you here.
    Consider using a drop down calendar (not particularly efficient if entering dates that have a large range, such as birthdays).

    • Luke M says:

      Good point. What I tend to do is format the cell to display results as:
      dd-mmm-yyyy

      That format is non-ambiguous, and would provide instant feedback to user that they entered data wrong. Not as strong as the CF solution above, but it tends to work for me.

      • RSPRasad says:

        Take any tool in this world Date fields are tricky and dealing with different date format is tiring, Excel dates are no exception.

        Though different regions follow different date formats but Month in three letter format is always safer. (dd-mmm-yyyy or mmm-dd-yyyy)

        I propose 1 date format(either of the above) across the world, developers can breath easy and Date field consumer will be free from ambiguities.

        One World - One Date

  2. Gary Lundblad says:

    One way I use conditional formatting is to make my workbooks, particularly data entry areas, cleaner looking. I hide a lot of things that are not relevant until they are. It makes for a very simple clean look that isn't overwhelming to others who use my workbooks, and it also has a nice effect when things just start to appear when they are needed. I also use it to hide zero values, again to make things cleaner looking. I absolutely love CF. I have run into problems where I've used a ton of CF. Excel seems to get buggy when handling some of my workbooks that use CF extensively. Has anyone else noticed this?

  3. Gary Lundblad says:

    I also use it for custom error messages, i.e. if a particular total does not equal another total, when it should, I will use an error message that just appears, in bold red font, on a black background, with a nice red dotted border around it. I don't want the error message there when everything is fine, so it just shows up when things are not fine. Or with cells that require input. They show up yellow until something is typed into them.

  4. Joel Diff says:

    Nice! Warnings are always a good thing to have, especially when the person entering the data is not the person who made the sheet.

    Regarding the "dates are numbers" issue, you could add a little extra to the conditional formatting formula to give an error for dates you don't want.

    For example, this formula will give you a warning if the text is not a date or if the date is earlier than January 1st, 2006:
    =OR(ISERROR(DAY($G2)),$G2<38718)

  5. Heather says:

    My solution for month confusion is in the cell formatting, 3/5/2000 changes to 5-Mar-2000 (and the users know this). I can quickly change it to whatever format I want later if needed.

  6. Athman says:

    Hi Chandoo,

    I am not understanding as to why you do Len(C3) = 0 and multiply by 0. 5. I think it should be implemented to make sure date is not appearing in the cell C3.

    • Luke M says:

      The 3 possibilities of C3 are:
      It's a date
      It's not a date
      It's blank

      For Chandoo's symbol then in D3, formula evaluates to 1 if it's a date, 0 if it's not a date, and if the cell is blank (aka, LEN(C3)=0 ) then it's 0.5, and displays the "!" symbol.

  7. Athman says:

    Thanks Luke..I have understood the logic

  8. Graham says:

    I gave up allowing users to input dates and wrote a date picker user form that is opened when the cell is selected.

  9. Gary Lundblad says:

    I would love to have a date picker! I downloaded an Excel add-on that supposedly did this, but it never worked. Graham, could you possibly share your date picker user form?

    Gary

    • Graham says:

      Gary. Yes you can have a copy of my date picker. The code may not be brilliant as I threw it together in response from a problem I was having with a windows update that affected Active X (as always meaning to revisit it later). The code comes from an old Access database I wrote and then I think the core from someone else's code years ago. I have created a spreadsheet that has the elements required as a demo for you. How do we transfer the file, will Chandoo act as an Intermediary?

      Graham

  10. Ryan Wells says:

    Conditional formatting for user input? That's pretty clever. I have a few templates I'm looking forward to trying this on

  11. pramod baviskar says:

    nice tip about conditional formatting. love to visit this site!!

  12. Gary Lundblad says:

    Thank you Graham!! Are you able to email the file to me?

    Gary

  13. Khalid NGO says:

    Hi Chandoo.
    Love the trick...
    Thanks.

    One question:
    If we enter 0 in C3,
    C3 becomes the date 00/01/1900
    and it goes pass through CF, it should not as 00/01/1900 is not a valid date.
    Is there any work around to catch this?

    Thank you and Regards,
    Khalid

  14. Gary Lundblad says:

    Thank you Graham. My email address is garylundblad@gmail.com

    Gary

  15. Take a look at Chapter 6 of my book - I don't use conditional formulas for dates - but I do use symbols for validation. The effect is the same.

  16. Gary Lundblad says:

    So sorry Graham! I've been buried and have not had a chance to look at it yet, but thank you, thank you, thank you for sending it. I should be able to look at it tomorrow. I am excited to try it out.

    Gary

  17. Andreas Andrianto says:

    Hi Graham, I'd love to get your date picker as well.

    Here is my address: aandrianto[at]gmail[dot]com

  18. Eugene says:

    Thank you, Chandoo, for such a great tip. Shared it with my Russian-speaking readers if you don't mind 🙂

  19. ZUR says:

    NICE TIP

  20. Brij Arora says:

    @LukeM and @chandoo

    once somebody copy paste the data then CF will not work.
    How can we handle this.

Leave a Reply