How to find overlapping date/time ranges in Excel?
In this article, I am going to talk about how to find or check if a date range or time range is overlapping with others in Excel as below screenshot shown.
Check overlapping date/time ranges with formula
Check overlapping date/time ranges with formula
1. Select the start date cells, go to Name Box to type a name and press Enter key to successfully give this range a ranged name. See screenshot:
2. Then go to select the end dates, and give it another ranged name in the Name Box and press Enter key. See screenshot:
3. Select a blank cell that you will place the judgement, and type this formula =SUMPRODUCT((A2<enddate)*(B2>=startdate))>1, and drag auto fill handle down to the cells with the formula. If the cell displays TRUE, it means the relative date range is overlapping with others, otherwise the data ranges are not overlapping with others.
In the formula, A2 and B2 are the date range you want to check, enddate and startdate are the ranged names you gave in above steps.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
![Screen Shot of Excel (with Office Tab installed)](http://cdn.extendoffice.com/images/stories/kte-module/officetab-bottom-02.gif)
![Gast](http://cdn.extendoffice.com/media/com_easydiscuss/images/default_avatar.png)
![GS](http://cdn.extendoffice.com/media/com_easydiscuss/images/default_avatar.png)
![Sunny](http://cdn.extendoffice.com/media/com_easydiscuss/images/default_avatar.png)
![zulfas1@gmail.com](http://cdn.extendoffice.com/media/com_easydiscuss/images/default_avatar.png)
![Sunny](http://cdn.extendoffice.com/media/com_easydiscuss/images/default_avatar.png)