IF Function

[agent707]

Hi everyone,

Need your advise to solve my problem as below example data;
ARR = Arrival DEP = Departure
A B C D
1 ARR DEP length of stay (night) Guest Name
2 26nov 2dec 6 Rihanna
3 30nov 5dec 5 Jackson
5 1dec 8dec 7 Melissa
6 8dec 10dec 2 Alisson

then I need to move based on the data above to below template per name of guest for month of December ONLY (some guests arrive on month before December), because I need to calculate quantity per date of staying only. I have tried IF function but haven't got correct calculation, please help to fill the formula which return to 1 (number) as bold below per period of stay as below I fill based on the guest list sequence above from Rihanna to Alisson. The cell without period of stay should be blank/0 (zero)

A B C D E F G H I J K L
8 1dec 2dec 3dec 4dec 5dec 6dec 7dec 8dec 9dec 10dec 11dec 12dec
9 1
10 1 1 1 1
11 1 1 1 1 1 1 1
12 1 1
13 3 2 2 2 1 1 1 1 1 (Grand TOTAL)

Your help would be much appreciated.
Thank you in advance.

 

[agent707]

The result 1 supposed to be below each date, e. g row 12 should be fill on H12 and I12, sorry because I can't post table/ excel and I rarely use this forum

 

[BrianJN1]

Ok, before I attempt anything I need to consider why #4 is missing in your data.
Was that an error on your behalf?

Then too, why is the data on just one sheet, as an example, or are you a small operation that everything can be provided on one sheet?
My reason for asking is that it would be wasteful in time to provide a solution for something that may be more lengthy than your example; ie need for two sheets.

 

[agent707]

Thanks for the reply.
It might be error when I was typing, thank you for the correction.
The actual data is on 12 sheets, as per month and more complex, thus I simplify as the example on my post.

 

[BrianJN1]

Thank you. So Sheet1 is input and that translates across to each month?

 

[agent707]

Thank you. So Sheet1 is input and that translates across to each month?

correct!

 

[BrianJN1]

Two last questions.
If I booked from Nov 28 to Dec 5, then 3 * 1 must show in Nov whilst the rest have to appear in Dec?

Do you expect that each monthly sheet is to be protected? I think you are asking that each cell in the Month sheets is to hold something like "=if(date1<date2, ...."="" for="" each="" client.
< date2 ...."
I need to look at this in the morning.</date2,>

 

[BrianJN1]

Ok. I've made a few assumptions as to how your sheets might appear.
I've taken your info above and called it Sheet1 (that appears in the posted image as an inset).

Sheet2 is for the month of December. I've assumed that you'd like to see the Client's name on the same row as their accommodation. The first Client, Rhianna, would also appear on the Nov sheet in a similar position.
https://www.dropbox.com/s/zde5bz5mhufnfdo/Accomm.jpg?dl=0

Have I missed anything? Is there more that you may require here? If, please advise.

 

[agent707]

Ok. I've made a few assumptions as to how your sheets might appear.
I've taken your info above and called it Sheet1 (that appears in the posted image as an inset).

Sheet2 is for the month of December. I've assumed that you'd like to see the Client's name on the same row as their accommodation. The first Client, Rhianna, would also appear on the Nov sheet in a similar position.
https://www.dropbox.com/s/zde5bz5mhufnfdo/Accomm.jpg?dl=0

Have I missed anything? Is there more that you may require here? If, please advise.

This is EXACTLY what I'm looking for, thank you so much for your effort.
Sorry for the late respond.
Have a great day.

 

[BrianJN1]

Glad to satisfy. If you have any worries feel free to call back.