Excel Formulas: Calculate dates overlap in days ~ A Complete Guide!!

Supposing some date ranges are recorded in a sheet, and now you want to calculate how many days or hours of those dates overlap with a specific date range as the below screenshot showed. Except to compare date ranges and calculate one by one, I introduce a formula to quickly solve this task in Excel here in this article. Let’s get started!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

General Formula:

• You can use the below formula to calculate the dates which are overlap by a given date in Excel.

=MAX(MIN(end1,end2)-MAX(start1,start2)+1,0)

Syntax Explanations:

• MAX – In Excel, the MAX function will return the largest numeric value from the range of input values.
• MIN – This function helps to return the smallest numeric value from the range of input values. Read more on the MIN function.
• End – It specifies the ending date.
• Start – It represents the starting date.
• Comma symbol (,) – It is a separator that helps to separate a list of values.
• Parenthesis () – The main purpose of this symbol is to group the elements.

Example:

Refer to the below example image.

• Here, we will enter the input values in Column B and Column C.
• Now we will find the overlapping of dates based on the criteria given in Column F.

• So, apply the above-given formula to the formula bar section and press the ENTER key.

• Finally, we will get the result in the selected cell.

Bottom-Line:

Hope you like this article to calculate how many days or hours of those dates overlap with a specific date range. Please share your query below in the comment box. We will assist you. Thank you so much for visiting our site. Click here to know more about Geek Excel *and Excel Formulas *!!

Read Ahead:

• Excel Formulas to Add Workdays to Date with Custom Weekends!!
• Excel Formulas to Add the Business Days to Date ~ Easy Guide!!
• Formulas to Round Prices to End in X.99 ~ Easy Tutorial!!