The picture below shows a table with data validation applied. If a user tries to enter an overlapping date range, the following error message appears.

How to create the data validation

If you apply data validation to a table, all new values you enter below will have the same data validation setting applied.

Data Validation

  1. Select all values in the table
  2. Go to tab "Data"
  3. Click "Data Validation" button
  4. Select Custom
  5. Type the following formula:
    =SUMPRODUCT((INDIRECT("Table1[@Start]")<=INDIRECT("Table1[End]"))* (INDIRECT("Table1[@End]")>=INDIRECT("Table1[Start]")))<=1
  6. Click OK

Customize Data Validation Error

  1. Select all values in the table
  2. Go to tab "Data"
  3. Click "Data Validation" button
  4. Go to tab "Error Alert"
  5. Enter a title
  6. Enter error message
  7. Click OK

Download excel *.xlsx file

Prevent users from entering overlapping date and time ranges.xlsx