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:
How to ensure cleaner dates with conditional formatting?
- Let’s say your users need to enter date input in cell C3.
- Select C3 and go to Home > Conditional Formatting > New Rule
- Select the rule type as “Use a formula…”
- Use a formula like this: =ISERROR(DAY($C$3))
- Set up formatting to highlight incorrect dates
- 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,
- Let’s say the date inputs go to column C, starting with C3 as first input
- Select all the cells in column C that will have dates
- Set up the conditional formatting with below rule:
- =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
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).
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.
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
There is a standard defined to tackle the hazzle with these different formats. http://www.iso.org/iso/home/standards/iso8601.htm
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?
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.
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)
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.
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.
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.
Thanks Luke..I have understood the logic
I gave up allowing users to input dates and wrote a date picker user form that is opened when the cell is selected.
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
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
Conditional formatting for user input? That’s pretty clever. I have a few templates I’m looking forward to trying this on
nice tip about conditional formatting. love to visit this site!!
Thank you Graham!! Are you able to email the file to me?
Gary
Gary. Yes can email you spreadsheet.
Graham
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
Thank you Graham. My email address is garylundblad@gmail.com
Gary
Gary,
Have sent file. Did you get it?
Grahan
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.
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
Hi Graham, I’d love to get your date picker as well.
Here is my address: aandrianto[at]gmail[dot]com
Thank you, Chandoo, for such a great tip. Shared it with my Russian-speaking readers if you don’t mind 🙂
NICE TIP
@LukeM and @chandoo
once somebody copy paste the data then CF will not work.
How can we handle this.