Difference between two dates

[Dochia]

Hi there,

I need help calculating the difference between two dates. I need to find out how many days a patient has been in our facility based on the entry /discharge dates within the month of November 2021 for example. Below are the entry/discharge dates and the number of days I need to obtain. I also included the formula which I tried, however, doesn't work properly under all scenarios. Any help/comment is much appreciated. Many thanks.

 

[6StringJazzer]

It's too bad you did not show row and column headers. It is always helpful to paste in actual data, better than pictures. I will assume Beginning Period is P5, and End Period is Q5, and Entry Date is column M and Discharge is N.

Your last example of 14 is incorrect, should be 15.

Note that my dates are in U.S. format.

$scratch.xlsm
	M	N	O	P	Q
7				Beginning	End
8				11/1/2021	11/30/2021
9
10	Entry	Discharge	No of Days
11	10/1/2021		30
12	11/16/2021	12/21/2021	15
13	11/3/2021	11/26/2021	24
14	11/1/2021		30
15	9/28/2021	11/15/2021	15
Stay Duratoin

Cell Formulas
Range	Formula
O11:O15	O11	=MIN(N11,$Q$8)-MAX(M11,$P$8)+1

 

[Dochia]

Hi , Sorry about not including the rows numbers, and I forgot to mention : when the pacient is discarged we do not count the day because we don't charged them: they need to stay overnight in order for us to charged them .So for the last I need to get 14 days not 15 days , please.

Thank you for your swift reply and help!!

 

[6StringJazzer]

OK try this

$scratch.xlsm
	M	N	O	P	Q
7				Beginning	End
8				11/1/2021	11/30/2021
9
10	Entry	Discharge	No of Days
11	10/1/2021		30
12	11/16/2021	12/21/2021	15
13	11/3/2021	11/26/2021	23
14	11/1/2021		30
15	9/28/2021	11/15/2021	14
Stay Duratoin

Cell Formulas
Range	Formula
O11:O15	O11	=MIN(N11,$Q$8)-MAX(M11,$P$8)+IF(OR(N11<=$P$8,N11>=$Q$8),1,0)

 

[Dochia]

Thank you so much !!!Much appreciated your swift reply and help !!

 

[Peter_SSs]

Are you sure that works how you want?

Below I have used that formula in column O and by my understanding the last two values are incorrect?
If I am right then an alternative version is given in column P

22 05 02.xlsm
	M	N	O	P	Q
7				Beginning	End
8				1/11/2021	30/11/2021
9
10	Entry	Discharge	6SJ	PSSs
11	1/10/2021		30	30
12	16/11/2021	21/12/2021	15	15
13	3/11/2021	26/11/2021	23	23
14	1/11/2021		30	30
15	28/09/2021	15/11/2021	14	14
16	28/09/2021	1/11/2021	1	0
17	3/11/2021	30/11/2021	28	27
Days

Cell Formulas
Range	Formula
O11:O17	O11	=MIN(N11,$Q$8)-MAX(M11,$P$8)+IF(OR(N11<=$P$8,N11>=$Q$8),1,0)
P11:P17	P11	=MIN(N11,Q$8)-MAX(M11,P$8)+OR(N11<P$8,N11>Q$8)

 

[Dochia]

=MIN(N11,Q$8)-MAX(M11,P$8)+OR(N11<P$8,N11>Q$8)

Yes you are right!!! Many thanks, much appreciated !!

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.