Return date overlap in days

This tutorial shows how to return the number of days that overlap between two date ranges using an Excel formula, with the MAX and MIN functions

EXCEL FORMULA 1. Return date overlap in days

EXCEL

Return date overlap in days

=MAX(MIN($C$6,C9)-MAX($C$5,B9)+1,0)
GENERIC FORMULA

=MAX(MIN(end_date1,end_date2)-MAX(start_date1,start_date2)+1,0)

ARGUMENTS
end_date1: The period of interest end date.
end_date2: The end date that you are assessing against the period of interest.
start_date1: The period of interest start date.
start_date2: The start date that you are assessing against the period of interest.

EXPLANATION

This tutorial shows how to calculate the number of days that overlap between two dates through the use of the MAX and MIN functions.
Excel represents dates as serial numbers therefore by calculating the minimum end date and maximum start date and then subtracting the earlier date from the later date, with addition of 1, the formula derives with the overlap days.

However, in a scenario where the dates don't overlap the formula returns a negative value, therefore, we are using the MAX function to isolate negative values and return a value of zero (0).

RELATED TOPICS

Related Topic Description Related Topic and Description
Return workdays in a month How to return the number of working days in a month

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
MAX Function The Excel MAX function returns the largest value from a specified range of numeric values
MIN Function The Excel MIN function returns the smallest value from a specified range of numeric values