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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
468
dmt32
D
K
  • Question Question
Trying to make a formula to figure out what total hours in the month will be, based on what I already know
Replies
1
Views
50
Sufiyan97
Sufiyan97
F
  • Solved
How to change value from data validation based on value in col A?
Replies
5
Views
335
Eric W
Eric W
C
  • Question Question
if Function
Replies
3
Views
281
ChetanPuri
C
Z
  • Solved
add number of working days to a specific date (Start Date) to have the (End Date)
Replies
5
Views
365
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

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