Find the intersection of 4 periods

[Flyingjoblo]

Hi everyone,
I am looking for a way to find the number of days at the intersection of 4 different periods using max/min formulas as stated in an old thread of 2009.
Anyone interested to help?
Exemple:
How many days included in all of the following periods:
1/3/2023-5/4/2025
6/6/2022-8/10/2030
9/1/2025-15/1/2025
10/2/2028-20/2/2028

 

[James006]

Hi,

To calculate date overlap in days, you have not indicated your reference period ...

 

[James006]

As far as the generic Overlap formula is concerned... with Start1 and End1 for your Reference Date :

=MAX(MIN(End1,End2)-MAX(Start1,Start2)+1,0)

 

[Flyingjoblo]

Thank you James006 for your reply. The reference is a 5th period.

ie : 1/4/2024-2/10/2027

 

[Flyingjoblo]

The result is 7

 

[James006]

Thanks for the Reference Period

When you say 7 is the result ... it is the result of your Reference Period versus your third item in your list : 9/01/2025 -15/01/2025

Otherwise, you will get : 370 days and 1'280 days for first and second periods ...

 

[Flyingjoblo]

Not exactly. Period 1, Period 2, Period 3 & Period 4 have only 7 days in common in the reference period (1/4/2024-2/10/2027)

 

[Flyingjoblo]

my apologies... period 4 is outside of the ref period. result is 0 in that particular case.

Let's assume P4 (11/1/2025-17/2/2025) instead. Result becomes 5.

 

[James006]

With the generic formula in post #3 ... you can perform all the calculations you need ...

 

[kevin9999]

Book1
	A	B	C
1	1/03/2023	5/04/2025	period 1
2	6/06/2022	8/10/2030	period 2
3	9/01/2025	15/01/2025	period 3
4	10/02/2028	20/02/2028	period 4
5
6	1/04/2024	2/10/2027	reference period
7
8	7	Is this it?
9
Sheet1

Cell Formulas
Range	Formula
A8	A8	=MINIFS(B1:B4,B1:B4,"<="&B6)-MINIFS(A1:A4,A1:A4,">="&A6)+1