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

Lookup and Reference Examples

  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • How to get address of named range in Excel
  • How to retrieve first match between two ranges in Excel
  • How to get last row in text data in Excel
  • INDEX function: Description, Usage, Syntax, Examples and Explanation

Data Analysis Examples

  • How to sum a total in multiple Excel tables
  • How to Create Gantt Chart in Excel
  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • Filter Data Based on Date in Excel
  • How to combine 2 or more chart types in a single chart in Excel

Data Validation Examples

  • Excel Data validation number multiple 100
  • Excel Data validation whole percentage only
  • Excel Data validation with conditional list
  • Prevent invalid data entering in specific cells
  • Excel Data validation specific characters only

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:   Split text and numbers 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:   Max of every nth column 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:   9 Mathematical Computations Example using Excel Statistical Function

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
  • How to split text string at specific character in Excel
  • How to extract word containing specific text in Excel
  • FIXED function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate Number of Instances in Excel
  • CONCATENATE function: Description, Usage, Syntax, Examples and Explanation
  • Sum through n months in Excel
  • Convert date to month and year in Excel
  • Convert Unix time stamp to Excel date
  • Convert text date dd/mm/yy to mm/dd/yy in Excel
  • Calculate time difference in hours as decimal value in Excel
  • How to calculate Net Present Value (NPV) in Excel
  • SYD function: Description, Usage, Syntax, Examples and Explanation
  • AMORLINC function: Description, Usage, Syntax, Examples and Explanation
  • PPMT function: Description, Usage, Syntax, Examples and Explanation
  • PMT, RATE, NPER, PV and FV Financial Functions in Excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles