# Need help with arrival, departure and date ranges

#### katalyzt_94

##### New Member
Hello everyone,

I need help calculating total stays for a date range. I have several transactions that included an arrival and departure date and I need to figure out number of stays for each date. Example below

Transaction 1, arrival 1/1/2015, departure 1/7/2015
Transaction 2, arrival 1/2/2015, departure 1/5/2015
Transaction 3, arrival 1/28/2015, departure 2/4/2015
Transaction 4, Arrival 2/3/2015, departure 2/5/2015

I NEED TO FIGURE OUT THE NUMBER OF TRANSACATIONBS FOR DATE RANGE 1/1/2015-2/28/2015. The departure date should NOT be included on the date that it is listed.

1/1/2015 1
1/2/2015 2
1/3/2015 2
1/4/2015 2
1/5/2015 1
1/6/2015 1
1/7/2015 1

and so forth through the end of February. Is there a way I can calculate this using a formula?

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
This does what you're after, although I have a different result for 7th Jan:
Excel Workbook
ABCDEFGH
1Header 1Type 1Arrive DateType 2Depart date
2Transaction 1arrival01/01/2015departure07/01/201501/01/20151
3Transaction 2arrival02/01/2015departure05/01/201502/01/20152
4Transaction 3arrival28/01/2015departure02/04/201503/01/20152
5Transaction 4Arrival03/02/2015departure05/02/201504/01/20152
605/01/20151
706/01/20151
807/01/20150
Sheet1

Sorry for the late response. This worked PERFECT! Thank you for your help!

Replies
12
Views
1K
Replies
4
Views
170
Replies
6
Views
280
Replies
3
Views
763
Replies
1
Views
207

1,196,116
Messages
6,013,563
Members
441,771
Latest member
clamnets

### 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?

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