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?

 

[awoohaw]

Happy you found a solution. Just a caution about using INDIRECT and TODAY. These are volatile functions and if you have really big workbook they will slow your workbook down. For 20 ROWS this should not be a problem.

the one thing I don't understand though is why are you using TODAY? Your values will change every day when you open the workbook. Is that what you want?

 

[ORDub]

It was something I found online and seemed to work...no other reason. This is for an AirBnb, so I'll have more than 20 rows for sure. When I tried your formula, I got the Value error. Here is what I have:

=IF(MONTH($A27)=MONTH(P$5),
IF(MONTH(P$5)=MONTH($B27),$B27-$A27+1,EOMONTH($A27,0)-$A27+1),
IF(MONTH(P$5)=MONTH($B27),$B27-P$5+1,""))

A and B are in my/out dates....P is my month

 

[ORDub]

Wait...got yours working by changing my Aug to 8/31/23....but now September shows negative days (8/29-9/2 in my example)

 

[awoohaw]

Wait...got yours working by changing my Aug to 8/31/23....but now September shows negative days.

Yes, I used actual date values in the month header column formatted as mmm-yy. And then use the MONTH function to get the month for the formulas. You seem to use a TEXT value for month, which works fine, but you can't really do date comparisons easily unless you extract the month as text.

 

[ORDub]

Yes, I used actual date values in the month header column formatted as mmm-yy. And then use the MONTH function to get the month for the formulas. You seem to use a TEXT value for month, which works fine, but you can't really do date comparisons easily unless you extract the month as text.

Not sure if you saw my edits....got yours working, but if I span 2 months, the second month is negative.

 

[awoohaw]

Not sure if you saw my edits....got yours working, but if I span 2 months, the second month is negative.

right, I put that warning in my first post. I thought you said you did not span 2 months, maybe I'm mistaken. But, that can be accounted for with additional IF statements.

 

[ORDub]

right, I put that warning in my first post. I thought you said you did not span 2 months, maybe I'm mistaken. But, that can be accounted for with additional IF statements.

Sorry, I missed that part. And yes, I'll regularly cross months....its for an AirBnB.

 

[awoohaw]

OKay, try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Check IN	Check OUT	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	# Calendar Months
2	2023-01-31	2023-03-12	1	28	12										2
3	2023-07-19	2023-08-27							13	27					1
4	2023-05-26	2023-06-28					6	28							1
5	2023-06-02	2023-06-23						22							0
6	2023-07-30	2023-09-05							2	31	5				2
7	2023-10-15	2023-11-11										17	11		1
8	2023-07-17	2023-08-17							15	17					1
9	2023-09-02	2023-10-10									29	10			1
10	2023-09-21	2023-10-27									10	27			1
11	2023-07-25	2023-08-14							7	14					1
12	2023-06-16	2023-07-12						15	12						1
13	2023-08-28	2023-09-21								4	21				1
14	2023-01-15	2023-02-17	17	17											1
15	2023-04-20	2023-05-11				11	11								1
16	2023-10-26	2023-11-21										6	21		1
17	2023-07-06	2023-07-30							25						0
18	2023-07-27	2023-08-16							5	16					1
19	2023-05-01	2023-05-21					21								0
20	2023-09-13	2023-10-08									18	8			1
21	2023-08-07	2023-09-11								25	11				1
Sheet1

Cell Formulas
Range	Formula
C2:N21	C2	=IF(MONTH($A2)=MONTH(C$1), IF(MONTH(C$1)=MONTH($B2),$B2-$A2+1,EOMONTH($A2,0)-$A2+1), IF(MONTH(C$1)=MONTH($B2),$B2-C$1+1, IF(AND(MONTH(C$1)>MONTH($A2),MONTH(C$1)<MONTH($B2)),EOMONTH(C$1,0)-C$1+1,"")))
O2:O21	O2	=MONTH(B2)-MONTH(A2)

 

[ORDub]

Thank you!

 

[awoohaw]

My pleasure. Best Wishes.