Calculating number of days from each month, from a range for a short term rental model

[ORDub]

The example is that I would have Column A as the date someone checks into a short term rental (lets say August 26), and Column B is the date the check out (lets say September 6). So in this, they stayed 5 nights in August, and 5 nights in September (they leave on the 6th)....if I have 20 rows of ranges like this, I need to be able to create a table that shows the nights stayed each month.

Thoughts?

 

[Peter_SSs]

Just figured it out...

=SUMPRODUCT(--(TEXT(ROW(INDIRECT($A6&":"&IF($B6="",TODAY(),$B6-1))),"mmm")=Q$5))

Could I suggest a small change to your layout and a non-volatile & slightly simpler formula?

Instead of text dates "Jan", "Feb" etc as you apparently have (& I have in Q5:AB5 below), use actual dates being the first day of each month and format those cells as "mmm" (as I have in C5:N5 below)
I have your formula in columns Q:AB and the simpler alternative in columns C:N and you will see they are producing identical results.

23 08 31.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
5	Check IN	Check OUT	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec			Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
6	31/01/23		1	28	31	30	31	30	31	31	0	0	0	0			1	28	31	30	31	30	31	31	0	0	0	0
7	19/07/23	27/08/23	0	0	0	0	0	0	13	26	0	0	0	0			0	0	0	0	0	0	13	26	0	0	0	0
8	26/05/23	28/06/23	0	0	0	0	6	27	0	0	0	0	0	0			0	0	0	0	6	27	0	0	0	0	0	0
9	2/06/23	23/06/23	0	0	0	0	0	21	0	0	0	0	0	0			0	0	0	0	0	21	0	0	0	0	0	0
10	30/07/23		0	0	0	0	0	0	2	31	0	0	0	0			0	0	0	0	0	0	2	31	0	0	0	0
11	15/10/23	11/11/23	0	0	0	0	0	0	0	0	0	17	10	0			0	0	0	0	0	0	0	0	0	17	10	0
12	17/07/23	17/08/23	0	0	0	0	0	0	15	16	0	0	0	0			0	0	0	0	0	0	15	16	0	0	0	0
13	2/09/23	10/10/23	0	0	0	0	0	0	0	0	29	9	0	0			0	0	0	0	0	0	0	0	29	9	0	0
ORDub

Cell Formulas
Range	Formula
C6:N13	C6	=MAX(MIN(EOMONTH(C$5,0),IF($B6="",TODAY(),$B6-1))-MAX(C$5,$A6)+1,0)
Q6:AB13	Q6	=SUMPRODUCT(--(TEXT(ROW(INDIRECT($A6&":"&IF($B6="",TODAY(),$B6-1))),"mmm")=Q$5))

 

[awoohaw]

@Peter_SSs , Isn't TODAY() also a volatile function? Or is it volatile in only certain situations?

 

[Peter_SSs]

@Peter_SSs , Isn't TODAY() also a volatile function? Or is it volatile in only certain situations?

Yes, you are absolutely correct, TODAY() is also volatile. I'm so used to focussing on INDIRECT and OFFSET.
Still, I think the formula is probably a better one and =TODAY() could be put in a single cell somewhere else & use that cell reference in the formula instead of the actual TODAY function.