IF/And Nestled function relating to DATE

Hi,

I need assistance with the IF/And function

B1 = Start Date "ex 16 Feb 15"
C1 = End Date ex. 22 Feb 15"

E5 = arrival date ex 31 jan 15
F5 = Departure Date ex 10 mar 2015

J5 = ? I want to calculate the number of days from the start to end date.

Below is what i have so far but it doesn't calculate correctly:

=IF(AND(E5<B1,F5>C1),"7",F5-(E5-B1))

If E6 is 31 Jan 15 and F6 is 20 Feb 15 then J6 should be 4, but above formula doesnt work.

Is not clear what you're trying to do, are you need to calculate distance between 2 dates between B1 and C1? And what correlation with arrival and departure dates? what the logic?

Hi Azumi,

Sorry for the confusion.

I have attached a sample spreadsheet

I work in a hotel. I need to calculate the revenue in a certain week period.

Week period is my B1 and C1.

If a guest stayed from 31st of Jan until 28 of Feb. On week period 23 feb until 01 mar, the weekly room nights column J5 should be 5 nights.

Ok see this, hope this works, using array formula (means press together CTRL+SHIFT+ENTER button)

Hi Azumi, if you change the week period. it doesn't work.

what do you mean by CTRL+SHIFT+ENTER button ? i tried but nothing happened.