Hi all,
I need to modify the formula below so that it works with dates set up as Jan-12, Feb-12, etc....
As it is, the formula works perfectly when the columns have dates written as "Jan, Feb, Mar, Apr...."
I got this formula from "duanzhuanming" when I posted the problem here: http://www.excelforum.com/excel-form...20#post3255820
Since then, I had to change the columns with the date format mentioned above (ie: Jan-12, Feb-12,....).
The current formula as it stands is: =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,"")))
Note: In some worksheets where this formula will be applied, the dates columns will not necessarily start with Jan-12. It may be Jul-12 onwards. So I need the formula to recognize the correct date of arrival and date of departure and split the nights spent accordingly.
Bookmarks