Date limitations using formulas

Keyboard Shortcuts

Data Validation's Date option doesn't cover all date definitions, like day of the week and comparing entries with dates in other columns. For these situations, select the Custom option to combine various date restrictions, e.g., use =AND(YEAR(B1)=2016,WEEKDAY(B1)<>1) to restrict all entries to be in 2016 and not Sunday.

- [Instructor] Sometimes when trying to set up…data validation rules that require dates,…you run into problems with the basic definition.…As we look at this list here…on the worksheet called DateFormulas,…maybe all we're trying to set up initially is…we want the shipping date to always be…more than two days later than the order date,…based on the fact that in our organization here…based on the products that we handle,…there's some assembly required,…some packaging before these items can be shipped.…So for example, that first entry here,…this must be more than two days later…so it's gotta at least the 14th of January or later.…

Now, if we select Column C…and go Data Validation, then choose Date,…we don't really have that capability there.…We have the capability to set up dates…between a starting date and an ending date.…We can't quite use that there.…And furthermore, we do want to make sure…that all these dates are in 2016.…So sometimes what we need to do,…and even though we are about to put in…some date restrictions, we don't choose date here,…

Resume Transcript Auto-Scroll

Author

Released

9/19/2016

With Excel's data validation tools, you can control how users enter data into workbooks, ensuring that data is consistent and accurate. You can control dates, times, even the length of the text they enter, or simply provide a list of acceptable choices to eliminate any possible mistakes. Here, Dennis Taylor explores how the data validation tools in Excel 2016 can be used to set dropdown lists and control numeric, text, date, and time entries.