Prevent overlapping date and time ranges using data validation
The picture above shows an Excel Table with Data Validation applied. An error dialog box appears if a user tries to enter an overlapping date and time range.
The image above shows an error dialog box that showed up because the date range 1/2/12 12:00 PM - 1/2/12 3:00 PM overlaps 1/2/12 11:00 AM - 1/2/12 1:00 PM.
Both the start and end of the date range must be entered to trigger the error dialog box provided the ranges overlap.
Data Validation is a feature in Excel that allows you to check values entered in your worksheet, this webpage explains it in great detail: What is Data Validation?
I am going to show you how to use a Data Validation formula in this article that checks date ranges and will warn the user if the date ranges overlap.
Create Data Validation with a custom formula
If you apply Data Validation to an Excel defined Table, all new values you enter below the last value in the Excel Table will have the same Data Validation setting applied.
In other word, the Excel table copies the Data Validation automatically to new cells when the Excel Table grows which is great and saves you time.
- Select all values except the headers in the Excel Table.
- Go to tab "Data" on the ribbon.
- Press with left mouse button on "Data Validation" button.
- Press with left mouse button on the drop-down list below "Allow:" and select "Custom", see image above.
- Type or copy/paste the following formula:
=SUMPRODUCT((INDIRECT("Table1[@Start]")<=INDIRECT("Table1[End]"))* (INDIRECT("Table1[@End]")>=INDIRECT("Table1[Start]")))<=1
- Press with left mouse button on OK button to apply Data Validation.
To be able to use Excel defined Tables in Data Validation formulas you have a few options which I have described in this article: How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas
One workaround demonstrated in the article is to use the INDIRECT function each time you reference an Excel Table, I will now explain the formula in great detail.
Explaining Data Validation formula in cell C5
A great feature with Excel Tables is that a reference pointing to an Excel Table doesn't change (unless you change the Excel Table name or headers) even if the Table grows or shrinks, the reference stays the same.
By the way, they are called structured references and they behave differently than regular cell references.
If you want to see each calculation step using the Evaluate Formula tool then copy the formula and paste to any empty cell not adjacent to the Excel Table, we will delete it later on.
Go to tab "Formula" on the ribbon and press with left mouse button on the "Evaluate Formula" button, this opens a dialog box that allows you to see each step in the calculation.
Simply press with left mouse button on the Evaluate button located on the dialog box to go to next step, keep press with left mouse button oning to see all calculation steps.
Step 1 - Check if start date is smaller than or equal to the end dates
Table1[@Start] is a reference to a cell on the same row as the selected cell and in column Start in Table1.
The less than sign and the equal sign combined checks if the value in the cell described above is less than or equal to the value in column End in Table1.
The less than and equal sign are logical operators and return a boolean value TRUE or FALSE. Table1[@Start] is a reference to a cell in column Start that is located on the same row as the current cell, in this case, cell C5. The @ character in Table1[@Start] means it is on the same row as the selected cell.
An Excel date is actually a number formatted as a date, 1/1/1900 is 1 and 1/1/2000 is 36526. There are 36525 days between 1/1/1900 and 1/1/2000.
Time is a fraction of a day, one hour is 1/24 and is approx. 0.041667 and 24 hours is 1. 12:00 PM is 0.5.
INDIRECT("Table1[@Start]")<=INDIRECT("Table1[End]")
becomes
"1/2/2012 12:00 PM"<={"1/1/12 12:00 PM", "1/2/12 1:00 PM", "1/2/12 3:00 PM"}
becomes
40910.5<={40909.5; 40910.5416666667; 40910.625}
and returns {FALSE; TRUE; TRUE}.
Step 2 - Check if end date is larger than or equal to the start dates
INDIRECT("Table1[@End]")>=INDIRECT("Table1[Start]")
becomes
"1/2/2012">={"1/1/12 8:00 AM", "1/2/12 11:00 AM", "1/2/12 12:00 PM"}
becomes
40910.625>={40909.3333333333; 40910.4583333333; 40910.5}
and returns {TRUE; TRUE; TRUE}.
Step 3 - Apply AND logic and sum array
The parentheses let you control the order of calculations, the asterisk multiplies the arrays which means that we apply AND logic meaning both values on the same row must return TRUE in order to return TRUE.
When you multiply boolean values Excel converts the output to their numerical equivalents, TRUE = 1 and FALSE = 0 (zero).
SUMPRODUCT((INDIRECT("Table1[@Start]")<=INDIRECT("Table1[End]"))* (INDIRECT("Table1[@End]")>=INDIRECT("Table1[Start]")))
becomes
SUMPRODUCT({FALSE; TRUE; TRUE}*{TRUE; TRUE; TRUE})
becomes
SUMPRODUCT({0; 1; 1})
and returns 2.
Step 4 - Check if value is less or equal to 1
If the number returned from the SUMPRODUCT function is larger than 1 we know that there is an overlapping date range.
The formula compares all ranges so the range we entered is also calculated, this makes the formula always return 1 for that row. That is why we need to check if the value is greater than 1.
SUMPRODUCT((INDIRECT("Table1[@Start]")<=INDIRECT("Table1[End]"))* (INDIRECT("Table1[@End]")>=INDIRECT("Table1[Start]")))<=1
becomes
2<=1 and returns FALSE. The error dialog box is shown.
Customize Data Validation Error Alert
- Select any value in the Excel Table.
- Go to tab "Data" on the ribbon.
- Press with left mouse button on the "Data Validation" button and a dialog box appears.
- Go to tab "Error Alert", shown in the image above.
- Enter a title.
- Enter error message. You can also choose a style.
- Press with left mouse button on OK button.
Data validation category
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]
Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]
A drop-down list in Excel prevents a user from entering an invalid value in a cell. Entering a value that […]
This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on […]
I will in this article demonstrate how to use a value from a drop-down list and use it to do […]
I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]
This article describes how to create a map in Excel, the map is an x y scatter chart with an […]
Josh asks: I have this working right now with 6 drop downs/lists. I wanted to see if you possibly know […]
Aynsley Wall asks: I have a spreadsheet that I use for 3 different companies. What I would really like to […]
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]
The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]
Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that […]
Overlapping category
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
This article demonstrates formulas that show if a date range is overlapping another date range. The second section shows how […]
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]
The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]
This article demonstrates event code combined with Conditional Formatting that highlights overlapping date ranges based on the selected date range. […]
This article explains how to build an array formula that sums numerical ranges. Example, I want to know how to […]
This article explains how to create a formula that returns the earliest (smallest) and the latest (largest) date of overlapping […]
This blog article describes how to extract coinciding date ranges using array formulas, the image above shows random date ranges […]
This article demonstrates how to calculate overlapping numerical ranges. What is interesting to know is the fact that Excel handles […]
This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and […]
The worksheet above shows four different time ranges in column B and C, the formula in cell C10 counts the […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.