1

I would like to use data validation to ensure someone has entered a valid date, but I'd also like them to be able enter TBD - just those two options. Is this at all possible without VBA? Is there a custom formula I could enter into the data validation tool?

8
  • How much control are you exercising over the input (free-form date entry in one cell, separate cells for month and day, etc.)? Do you include a formatting hint (like "enter as MM/DD/YYYY"). If it is being entered as a system recognizable date, it could be simple. It would be stored as a number and you could test whether that value is between validation limits. Use nested IFs to also look for TBD or blank (use the UPPER or LOWER function on the cell for the TBD check so capitalization isn't an issue).
    – fixer1234
    Oct 8, 2014 at 17:09
  • By date validation, do you mean anything Excel recognizes as date data and/or a date that makes sense? (would you accept a date that is decades in the past or in the future?)
    – fixer1234
    Oct 8, 2014 at 19:18
  • Just saw this. I'll try checking the nested IFs using the date's numberical value. Thank you! (Heading out now but I'll let you know tomorrow if it worked). Oct 8, 2014 at 20:51
  • Any ideas what's wrong? I tried adding this as my custom formula on the Data Validation settings tab and it accepted the formula but creates an error on valid data entry into the cell: or(>44639,<94196,"TBD","tbd") Oct 9, 2014 at 14:37
  • The conditions have to evaluate to true or false, so put an equal sign in front of "TBD" and "tbd".
    – fixer1234
    Oct 9, 2014 at 14:39

1 Answer 1

3

You can do this by setting the cell format to Text and then setting up a custom data validation rule.

To set the cell format, right-click the data entry cell and choose Format Cells.... Then set the category to Text.

To set up custom data validation, select the data entry cell, click Data Validation on the Data ribbon. Set the Allow box to Custom. Then enter the formula below, where A1 is the address of your data entry cell.

=OR(A1="TBD",NOT(ISERROR(DATEVALUE(A1))))

This will allow entry of "TBD" or anything that Excel automatically recognizes as a date. This may allow some irregular formats you did not have in mind, such as mm/dd-yyyy or m-d. You'll need to take a different approach if you want only certain date formats to be allowed.

7
  • Thanks but I need a valid date entry for other computations so storing as text not an option. Oct 9, 2014 at 14:38
  • @T.Sullivan Date computations should still work even if the cell is formatted as text. Try it.
    – Excellll
    Oct 9, 2014 at 14:46
  • Okay, tried this but when I change columns to TEXT format, the dates are displayed as their serial numbers. I need them displayed as dates. Any ideas? Oct 9, 2014 at 14:56
  • I should add that I am working with an existing worksheet that has many rows with dates already entered. Perhaps you meant to set up as text and then type in using date format from the beginning. I can probably find a way to convert my date columns to text while displaying in the date format... Oct 9, 2014 at 15:03
  • @T.Sullivan So, the text formatting only works as expected if it is a data entry cell and the data is entered only AFTER the Text format has been applied. Excel implicitly assigns formats to data entered in General format cells, hence the dates you already have entered are stored as serial numbers.
    – Excellll
    Oct 9, 2014 at 17:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .