Search code examples
excelvalidationexcel-2010excel-2007

Excel Data Validation - Allowing multiple data types

I wish to set up some data validation on a cell in Microsoft Excel such that a user is able to enter either a date or "N/A". How might I go about this? I suspect it will require me utilising the "Custom" data validation option but I am uncertain.

Thank you kindly for any advice you are able to provide.

Solution

  • Try the following formula for a custom Data Validation in cell A1:

    =A1=IFERROR(DATE(YEAR(A1),MONTH(A1),DAY(A1)),"N/A")

    This should to the trick. Let me know if you have any questions understanding the formula. Yet, the online help for all of these functions is pretty elaborate.