Difference between two dates, number of nights between a certain range of period

[BobsterT]

I want to know the number of nights people are staying between two dates. (in this example, it's between the 1st and the 31st of March.
If someone arrives the 28 of February and is leaving the 3rd of March, then the result should be 2, that is the night of the first and the second of March.

If someone is arriving the 30st of March and leaving the 1st of April, they stay the 30st and the 31st, thus 2.
If they leave the 31st of March, they stay one day, thus 1.

...
Is there anyone who can help me? I'm struggling with loads of what if's and I'm not getting the result I need.
Something more easier ?

Tx

Bob

 

[popipipo]

=MAX((MIN($B3,D$1)-MAX($A3,C$1)),0)

Try this formula in C3.
feb = 1-2-24
mrt =1-3-24
apr=1-4-24

 

[hrayani]

How about

Book1
	B	C	D
1	Range	From	1-Mar-24
2		To	31-Mar-24
3
4	Arrival	Departure
5	15-Feb-24	29-Feb-24	0
6	26-Feb-24	5-Mar-24	4
7	9-Mar-24	12-Mar-24	3
8	28-Feb-24	5-Apr-24	31
9	26-Mar-24	10-Apr-24	6
10	1-Mar-24	3-Mar-24	2
11	2-Mar-24	3-Mar-24	1
12	26-Mar-24	31-Mar-24	5
13	28-Mar-24	1-Apr-24	4
Sheet1

Cell Formulas
Range	Formula
D5:D13	D5	=MAX(0,MIN($D$2+1,C5)-MAX($D$1,B5))

 

[BobsterT]

My goodness, this is great. Thank you so much Hrayani.

 

[hrayani]

My goodness, this is great. Thank you so much Hrayani.

you are welcome & thanks for the feedback