Formula that will cascade the number of nights

[chesterrae]

I need to distribute the Number of nights (column C) to column E to column K (Sunday to Saturday). The starting point of cascading is based on its Check in Day (column B).

check in date	check in day	No. of nights		Sun	Mon	Tue	Wed	Thu	Fri	Sat
1/22/2017	Sunday	4		1	1	1	1
7/20/2017	Thursday	1						1
1/29/2017	Sunday	11		2	2	2	2	1	1	1
1/24/2017	Tuesday	1
2/7/2017	Monday	4

<tbody>
</tbody>

Any formula or other method that would make easier than manual intervention.


Thank you in advance!
Have a great day!

 

[Special-K99]

Why is 1/29/2017 2 2 2 2 1 1 1 ? Why can;t it be 2 2 2 2 2 1 blank ? Or another combination?
Why are they 2s?
Why do the 2s stop on Thursday?

You need to explain this since you haven't mentioned this in your description of the problem.

 

[istiasztalos]

Hi,

You'll need to have numbers instead of day names in the top row (or another row with numbers from 1 to 7 hidden above them...)

Then you can use an array formula based on the check in date, no of nights and the day numbers, assuming check-in is in column A, nights are Column C, and day numbers are in row 1, starting at D1, your formula looks like this:

=SUM(IF(WEEKDAY(ROW(INDIRECT($A2&":"&$A2+$C2-1)),1)=D$1,1,0))

After typing, enter the formula by hitting CTRL+SHIFT+ENTER ! this is very important, add this to the top right corner of the cascading table and then drag to right and to the bottom

checkin date	day	nights	1	2	3	4	5	6	7
27/09/2017	Wed	1	0	0	0	1	0	0	0
28/09/2017	Thu	3	0	0	0	0	1	1	1
29/09/2017	Fri	7	1	1	1	1	1	1	1
30/09/2017	Sat	11	2	2	2	1	1	1	2
01/10/2017	Sun	14	2	2	2	2	2	2	2
02/10/2017	Mon	5	0	1	1	1	1	1	0

<colgroup><col><col span="2"><col span="3"><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[chesterrae]

Hi Special-K99,

The check in day of 1/29/2017 is Sunday with 11 No. of Nights, that's why the cascading of 11 nights from Sunday to Saturday should start first at sunday. (looping from left to right)

 

[chesterrae]

Hi istiasztalos,

The formula works perfectly!
You're a genius!!

Thank you so much!

Hi,

You'll need to have numbers instead of day names in the top row (or another row with numbers from 1 to 7 hidden above them...)

Then you can use an array formula based on the check in date, no of nights and the day numbers, assuming check-in is in column A, nights are Column C, and day numbers are in row 1, starting at D1, your formula looks like this:

=SUM(IF(WEEKDAY(ROW(INDIRECT($A2&":"&$A2+$C2-1)),1)=D$1,1,0))

After typing, enter the formula by hitting CTRL+SHIFT+ENTER ! this is very important, add this to the top right corner of the cascading table and then drag to right and to the bottom

checkin date	day	nights	1	2	3	4	5	6	7
27/09/2017	Wed	1	0	0	0	1	0	0	0
28/09/2017	Thu	3	0	0	0	0	1	1	1
29/09/2017	Fri	7	1	1	1	1	1	1	1
30/09/2017	Sat	11	2	2	2	1	1	1	2
01/10/2017	Sun	14	2	2	2	2	2	2	2
02/10/2017	Mon	5	0	1	1	1	1	1	0

<tbody>
</tbody>

 

[istiasztalos]

I'm happy that helped have a nice day !