Formula that will cascade the number of nights

C

chesterrae

Board Regular
Joined
Dec 23, 2015
Messages
51
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).



[TABLE="width: 500"]
<tbody>[TR]
[TD]check in date[/TD]
[TD]check in day[/TD]
[TD]No. of nights[/TD]
[TD][/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[/TR]
[TR]
[TD]1/22/2017[/TD]
[TD]Sunday[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/20/2017[/TD]
[TD]Thursday[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/29/2017[/TD]
[TD]Sunday[/TD]
[TD]11[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1/24/2017[/TD]
[TD]Tuesday[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/7/2017[/TD]
[TD]Monday[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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


Thank you in advance!
Have a great day!
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
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.
 
Last edited:
Upvote 0
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

[TABLE="class: grid, width: 680, align: left"]
<colgroup><col><col span="2"><col span="3"><col><col><col span="2"></colgroup><tbody>[TR]
[TD]checkin date[/TD]
[TD]day[/TD]
[TD]nights[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD="align: right"]27/09/2017[/TD]
[TD]Wed[/TD]
[TD]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]28/09/2017[/TD]
[TD]Thu[/TD]
[TD]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]29/09/2017[/TD]
[TD]Fri[/TD]
[TD]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]30/09/2017[/TD]
[TD]Sat[/TD]
[TD]11[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]01/10/2017[/TD]
[TD]Sun[/TD]
[TD]14[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]02/10/2017[/TD]
[TD]Mon[/TD]
[TD]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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)
 
Upvote 0
Hi istiasztalos,

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

Thank you so much!





istiasztalos said:
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

[TABLE="class: grid, width: 680, align: left"]
<tbody>[TR]
[TD]checkin date[/TD]
[TD]day[/TD]
[TD]nights[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD="align: right"]27/09/2017[/TD]
[TD]Wed[/TD]
[TD]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]28/09/2017[/TD]
[TD]Thu[/TD]
[TD]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]29/09/2017[/TD]
[TD]Fri[/TD]
[TD]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]30/09/2017[/TD]
[TD]Sat[/TD]
[TD]11[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]01/10/2017[/TD]
[TD]Sun[/TD]
[TD]14[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]02/10/2017[/TD]
[TD]Mon[/TD]
[TD]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
VBA help - Locking & unlocking on certain times of day
2
Replies
17
Views
347
dmt32
D
F
  • Solved
How to change value from data validation based on value in col A?
Replies
5
Views
332
Eric W
Eric W
C
  • Question Question
if Function
Replies
3
Views
275
ChetanPuri
C
Z
  • Solved
add number of working days to a specific date (Start Date) to have the (End Date)
Replies
5
Views
294
Peter_SSs
Peter_SSs
A
  • Question Question
Copy & Paste from workbook to another with criterias
Replies
2
Views
498
Anthony86
A

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,632
Latest member
jladair

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top