Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to use Excel INDIRECT Function
  • How to get last column number in range in Excel
  • Count rows with at least n matching values
  • How to use Excel ROW Function
  • Left Lookup in Excel

Data Analysis Examples

  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • Filter Data Based on Date in Excel
  • Create Scatter Chart in Excel
  • Conditional Formatting New Rule with Formulas in Excel
  • Excel Line Chart

Data Validation Examples

  • Excel Data validation specific characters only
  • Excel Data validation must begin with
  • Excel Data validation allow weekday only
  • Excel Data validation with conditional list
  • Excel Data validation exists in list

Calculate date overlap in days in Excel

by

If you need to calculate the number of days that overlap in two date ranges, then this tutorials is for you.

You can use basic date arithmetic, together with the the MIN and MAX functions. See example below;

Worked Example:   Max if criteria match in Excel

Formula

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

Explanation

In the example shown, the formula in D6 is:

=MAX(MIN(end,C6)-MAX(start,B6)+1,0)

How this formula works

Excel dates are just serial numbers, so you can calculate durations by subtracting the earlier date from the later date.

Worked Example:   Find longest string with criteria in Excel

This is what happens at the core of the formula here:

MIN(end,C6)-MAX(start,B6)+1

Here are are simply subtracting an earlier date from a later date. To figure out what dates to use for each date range comparison, we use MIN to get the earliest end date, and MAX to get the latest end date.

Worked Example:   Excel MAX, MATCH and ADDRESS function illustration

Finally, we use the MAX function to trap negative values and return zero instead. Using MAX this way is a clever way to avoid using IF.

Post navigation

Previous Post:

Add business days to date in Excel

Next Post:

Calculate days remaining in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Remove file extension from filename in Excel
  • How to display conditional message with REPT function in Excel
  • Get position of 2nd 3rd and more instance of character in Excel
  • How to use double quotes inside a formula in Excel
  • T function: Description, Usage, Syntax, Examples and Explanation
  • Get day from date in Excel
  • How to calculate workdays per month in Excel
  • Count times in a specific range in Excel
  • Series of dates by day
  • Get last weekday in month in Excel
  • Calculate cumulative loan principal payments in Excel
  • CUMPRINC function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate annual compound interest schedule in Excel
  • TBILLPRICE function: Description, Usage, Syntax, Examples and Explanation
  • FVSCHEDULE function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles