How to validate the column in excel sheet.

Posted on 2012-09-15
Last Modified: 2012-10-03
I have an excel sheet where in I want the column A to be validated for following conditions.
1.It should accept only dates.
2.In order dd-mmm-yy ex: 5-Jan-12
3.Other than this should make the cell red.
4. The cell may be emoty.
5. But if the cell has values it should support this format only.
Question by:searchsanjaysharma
    LVL 25

    Expert Comment

    - apply the date format you want to the column. this will make most errors visible to the user as mistyped dates will appear invalid or mapped to a different date
    - use conditional formatting (available from the context menu relative to the cell) to make the bad cells red. check for a reasonable time range using DATEDIF()


    if you need the column to be a string, you can validate a date by building an excel date from the provided string using DATE() and various string functions, and then reformat the date as a string and check that both strings are identical. in this way, you let excel chack for valid dates in terms of number of days per month, 29th octoberts and such stuff
    LVL 10

    Accepted Solution

    Assuming your primary goal is to prevent non-dates/invalid dates, I'd recommend using Data Validation (on the Data tab) and date formatting, rather than trying to flag bad data.  "An ounce of prevention" and all that. See attached example.

    Note that other date formats can be entered (e.g., 9/15/2012), but will be converted to the desired format.  I set a Start Date of 9/15/2012 and an End Date of 12/31/2050, but these (and the input/error messaging) can be edited to whatever is appropriate.

    +1 to skull's discussion on string/date discussion

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now