Need assistance calculating number of days with several variables

[cmacnab]

Hi all. I need to calculate the number of days based on several variables and I just can't get them all in there.

This would be based on a reference period, for example:
Period 01/01/21 to 01/31/21

There could be several options that I need to cover in my formula, so 4 different dates are referenced:

Dates ending after reference period: from 01/19/21 to 02/18/21 = 13 days based on period above
Dates starting before reference period: from 12/15/20 to 01/03/21 = 3 days based on period above
Dates outside of reference period: from 02/01/21 to 02/28/21 = 0 days based on period above
Dates starting before & ending after reference period: from 12/01/20 to 02/28/21 = 31 days based on period above

I hope that I'm clear, can someone help me with this? Thank you in advance.

 

[jtakw]

Hi,

Does this help:

Book3.xlsx
	A	B	C	D	E	F
9	Reference Start	Reference End				Days
10	1/1/2021	1/31/2021		1/19/2021	2/18/2021	13
11				12/15/2020	1/3/2021	3
12				2/1/2021	2/28/2021	0
13				12/1/2020	2/28/2021	31
14				1/1/2021	1/31/2021	31
Sheet919

Cell Formulas
Range	Formula
F10:F14	F10	=MAX(0,DAYS(MIN(B$10,E10),MAX(A$10,D10))+1)