Formula to fill cells based on Date of Arrival and Date of Departure

[AH]

Hi all,

I'm using Excel 2010 and my problem is as follows:

I have a worksheet which contains data relating to "Date of Arrival" (column B) and "Date of Departure" (column C) for the period July 2012 to June 2013. The dates therein are in the format dd-mm-yyyy. Column D is used to calculate the length of stay using the DATEDIF formula.

Now the next 12 columns just after Column D are columns relating to each month of that period (and are headed Jul-12, Aug-12, Sept-12,.....,Jun-13). The aim is to spread that length of stay in each column concerned and I needa formula that will help me do this.

Example: If Date of Arrival is 29 July 2013 and Date of Departure is 03 August 2013 (ie: total of 5 nights), then I want the cell in Jul-13 to be filled by 3 and the cell in Aug-13 to be filled by 2.

I hope I managed to expose the problem correctly. Thank you for your help!

 

[Claus Busch]

Hi,

Am Wed, 19 Jun 2013 09:49:56 -0700 (PDT) schrieb AH:

I have a worksheet which contains data relating to "Date of Arrival" (column B) and "Date of Departure" (column C) for the period July 2012 to June 2013. The dates therein are in the format dd-mm-yyyy. Column D is used to calculate the length of stay using the DATEDIF formula.

Now the next 12 columns just after Column D are columns relating to each month of that period (and are headed Jul-12, Aug-12, Sept-12,.....,Jun-13). The aim is to spread that length of stay in each column concerned and I need a formula that will help me do this.

Example: If Date of Arrival is 29 July 2013 and Date of Departure is 03 August 2013 (ie: total of 5 nights), then I want the cell in Jul-13 to be filled by 3 and the cell in Aug-13 to be filled by 2.

please look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "Date"


Regards
Claus Busch