Formula to count number of nights falling in each month from arrival and departure dates

Hi all,

I'm using Excel 2010 and I have a table with arrival date and departure dates for individuals. I need to fill the corresponding columns relating to months with the number of nights spent. Say, if someone arrived on 26.02.13 and left on 05.03.13, 3 nights would go in the February Column and the remaining 4 nights would go into the March Column.
I can't find the right formula to do this.

Can a pivot table help me here?

I am enclosing the table for a clearer picture of the problem. sample.xlsx

Hi,
See attached file for your reference,

Hope this helps!

Thank you duanzhuanming! Works brilliantly.

Could explain the formula in plain english...I'm very interested to understand the logic behind. Hope it's not too much asking!

=IF((MONTH($B4)=COLUMN(F$1)-3)*(MONTH($C4)=COLUMN(F$1)-3),$C4-$B4,IF((MONTH($B4)=COLUMN(F$1)-3),EOMONTH($B4,0)-$B4+1,IF(MONTH($C4)=COLUMN(F$1)-3,$C4-EOMONTH($B4,0)-1,"")))

In this formula: Eomonth Function returns last day of a month
you must check the date of arrival and the date of departure ---

MONTH($B4)=COLUMN(F$1)-3)*(MONTH($C4)=COLUMN(F$1)-3)

if it's the same month(True)--> Date of departure-Date of arrival.
if false, you must check month of arrival and month of departure = ???? --> Last day of month -Date of arrival or Date of departure-Last day of month

Thank you duanzhuanming!

I'm facing a problem when pasting the formula in the original worksheet. I've changed the cell references accordingly but it's not displaying anything in the cells, not even giving a formula error whatsoever!

I'm enclosing an extract of the file...can you have a look please?

sample 2.xlsx

Hi,
Try this:

W2=IF((MONTH($L2)=COLUMN(W$1)-22)*(MONTH($M2)=COLUMN(W$1)-22),$M2-$L2,IF((MONTH($L2)=COLUMN(W$1)-22),EOMONTH($L2,0)-$L2+1,IF(MONTH($M2)=COLUMN(W$1)-22,$M2-EOMONTH($L2,0)-1,"")))

Because : Column(W$1)=23, jan= Column(W$1)-22....

Hello
Maybe a slightly shorter alternative in cell W2 copied across and down to AH11:

Please Login or Register  to view this content.

This however, uses the 'Volatile' Indirect function , so if you have a large data set it might slow things down and duanzhuanming's solution might prove better.

Hope this helps.
DBY

Thank you duanzhuanming! It worked perfectly!

Thank you DBY! I'll give your solution a try too!