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

O

ORDub

New Member
Joined
Apr 10, 2013
Messages
16
Platform
  1. MacOS
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?
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
It depends on the version of Excel you are using, for example below is one way it could be done in Excel 365:
Book1
ABCDEFGHIJKLMN
1Check InCheck OutJanFebMarAprMayJunJulAugSepOctNovDec
226/08/202306/09/2023 55
325/02/202304/04/2023 3313
422/05/202304/06/2023 93
5 
6 
7 
8 
9 
10 
11 
12 
13 
14 
15 
16 
17 
18 
19 
20 
21 
Sheet1
Cell Formulas
RangeFormula
C2:N21C2=LET( dts,TEXT(SEQUENCE($B2-($A2+1),,$A2+1,1),"mmm"), MAP(BYCOL($C$1:$N$1,LAMBDA(x,SUM(COUNTIF(x,dts)))),LAMBDA(x,IF(x=0,"",x))))
Dynamic array formulas.
 
Upvote 0
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Joe4 said:
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
Click to expand...
I am on a Mac...doesn't say which version it is. Just 16.75.2 (2019).
 
Upvote 0
Here is an older excel version possibility:
(and you probably don't even need to do the NETWORKDAYS.INTL function. Just subtract the dates.
Also, this won't work if the dates span more than 2 months.
Corrected:

Book1
ABCDEFGHIJKLMN
1Check INCheck OUTJan-23Feb-23Mar-23Apr-23May-23Jun-23Jul-23Aug-23Sep-23Oct-23Nov-23Dec-23
22023-01-312023-02-14114          
32023-07-192023-08-07      137    
42023-05-262023-06-08    68      
52023-06-022023-06-09     8      
62023-07-302023-08-12      212    
72023-10-152023-10-29         15  
82023-07-172023-07-31      15     
92023-09-022023-09-07        6   
102023-09-212023-10-02        102  
112023-07-252023-08-07      77    
122023-06-162023-06-29     14      
132023-08-282023-09-10       410   
142023-01-152023-01-2612           
152023-04-202023-04-26   7        
162023-10-262023-11-04         64 
172023-07-062023-07-26      21     
182023-07-272023-08-08      58    
192023-05-012023-05-20    20       
202023-09-132023-09-22        10   
212023-08-072023-08-14       8    
Sheet1
Cell Formulas
RangeFormula
C2:N21C2=IF(MONTH($A2)=MONTH(C$1), IF(MONTH(C$1)=MONTH($B2),NETWORKDAYS.INTL($A2,$B2,"0000000"),NETWORKDAYS.INTL($A2,EOMONTH($A2,0),"0000000")), IF(MONTH(C$1)=MONTH($B2),NETWORKDAYS.INTL(C$1,$B2,"0000000"),""))
 
Last edited:
Upvote 0
awoohaw said:
Here is an older excel version possibility:
(and you probably don't even need to do the NETWORKDAYS.INTL function. Just subtract the dates.
Also, this won't work if the dates span more than 2 months.
Corrected:

Book1
ABCDEFGHIJKLMN
1Check INCheck OUTJan-23Feb-23Mar-23Apr-23May-23Jun-23Jul-23Aug-23Sep-23Oct-23Nov-23Dec-23
22023-01-312023-02-14114          
32023-07-192023-08-07      137    
42023-05-262023-06-08    68      
52023-06-022023-06-09     8      
62023-07-302023-08-12      212    
72023-10-152023-10-29         15  
82023-07-172023-07-31      15     
92023-09-022023-09-07        6   
102023-09-212023-10-02        102  
112023-07-252023-08-07      77    
122023-06-162023-06-29     14      
132023-08-282023-09-10       410   
142023-01-152023-01-2612           
152023-04-202023-04-26   7        
162023-10-262023-11-04         64 
172023-07-062023-07-26      21     
182023-07-272023-08-08      58    
192023-05-012023-05-20    20       
202023-09-132023-09-22        10   
212023-08-072023-08-14       8    
Sheet1
Cell Formulas
RangeFormula
C2:N21C2=IF(MONTH($A2)=MONTH(C$1), IF(MONTH(C$1)=MONTH($B2),NETWORKDAYS.INTL($A2,$B2,"0000000"),NETWORKDAYS.INTL($A2,EOMONTH($A2,0),"0000000")), IF(MONTH(C$1)=MONTH($B2),NETWORKDAYS.INTL(C$1,$B2,"0000000"),""))
Click to expand...
#Value! error. :(
 
Upvote 0
try this:

Book1
ABCDEFGHIJKLMN
1Check INCheck OUTJan-23Feb-23Mar-23Apr-23May-23Jun-23Jul-23Aug-23Sep-23Oct-23Nov-23Dec-23
22023-01-312023-02-14114          
32023-07-192023-08-07      137    
42023-05-262023-06-08    68      
52023-06-022023-06-09     8      
62023-07-302023-08-12      212    
72023-10-152023-10-29         15  
82023-07-172023-07-31      15     
92023-09-022023-09-07        6   
102023-09-212023-10-02        102  
112023-07-252023-08-07      77    
122023-06-162023-06-29     14      
132023-08-282023-09-10       410   
142023-01-152023-01-2612           
152023-04-202023-04-26   7        
162023-10-262023-11-04         64 
172023-07-062023-07-26      21     
182023-07-272023-08-08      58    
192023-05-012023-05-20    20       
202023-09-132023-09-22        10   
212023-08-072023-08-14       8    
Sheet1
Cell Formulas
RangeFormula
C2:N21C2=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,""))
 
Upvote 0
Just figured it out...

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

Row 6 is the date ranges, row 5 are my month headers for the tally. Sorry, haven't figured out how to post spreadsheets here yet.

How do I mark this as resolved? And thank you to everyone who tried to help, I truly appreciate it!!!
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

K
  • Solved
Create macro that automatically goes to current date from a monthly calendar on each tab when file is opened
Replies
3
Views
702
KimC33
K
N
  • Question
2-step Dynamic Ranges and Charts
Replies
0
Views
231
Nick Loughton
N
B
  • Solved
Firing Pete Best
Replies
2
Views
335
BloodyBill
B
john Smith72
  • Solved
count days between dates for each month by drop list
Replies
9
Views
111
Tetra201
Tetra201
S
  • Question
Calculate phase costs from monthly forecasts
Replies
2
Views
242
felixstraube
F

Forum statistics

Threads
1,215,301
Messages
6,124,146
Members
449,145
Latest member
el_gazar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top