Break down date ranges

L

Legacy 351788

Guest
Hello,

From the below I have in cell A1:A31 dates. B1:B31 DOW and C1:C31 number of rooms sold. I am wanting a formula that firstly breaks the month down into weeks that run Fri-Thurs and then calculates the number of rooms sold during that week.

This will be done every month and so the DOW will change reflective to how they fall in the month and so the formula will need to account for this.

Is this possible?

[TABLE="width: 2371"]
<colgroup><col><col><col><col><col><col span="31"></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]01/10/2015[/TD]
[TD]02/10/2015[/TD]
[TD]03/10/2015[/TD]
[TD]04/10/2015[/TD]
[TD]05/10/2015[/TD]
[TD]06/10/2015[/TD]
[TD]07/10/2015[/TD]
[TD]08/10/2015[/TD]
[TD]09/10/2015[/TD]
[TD]10/10/2015[/TD]
[TD]11/10/2015[/TD]
[TD]12/10/2015[/TD]
[TD]13/10/2015[/TD]
[TD]14/10/2015[/TD]
[TD]15/10/2015[/TD]
[TD]16/10/2015[/TD]
[TD]17/10/2015[/TD]
[TD]18/10/2015[/TD]
[TD]19/10/2015[/TD]
[TD]20/10/2015[/TD]
[TD]21/10/2015[/TD]
[TD]22/10/2015[/TD]
[TD]23/10/2015[/TD]
[TD]24/10/2015[/TD]
[TD]25/10/2015[/TD]
[TD]26/10/2015[/TD]
[TD]27/10/2015[/TD]
[TD]28/10/2015[/TD]
[TD]29/10/2015[/TD]
[TD]30/10/2015[/TD]
[TD]31/10/2015[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/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] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]229[/TD]
[TD]248[/TD]
[TD]244[/TD]
[TD]154[/TD]
[TD]206[/TD]
[TD]241[/TD]
[TD]128[/TD]
[TD]232[/TD]
[TD]248[/TD]
[TD]238[/TD]
[TD]241[/TD]
[TD]220[/TD]
[TD]248[/TD]
[TD]114[/TD]
[TD]235[/TD]
[TD]247[/TD]
[TD]244[/TD]
[TD]248[/TD]
[TD]247[/TD]
[TD]239[/TD]
[TD]89[/TD]
[TD]246[/TD]
[TD]245[/TD]
[TD]230[/TD]
[TD]223[/TD]
[TD]245[/TD]
[TD]244[/TD]
[TD]109[/TD]
[TD]248[/TD]
[TD] [/TD]
[TD]6404[TABLE="width: 2371"]
<colgroup><col><col><col><col><col><col span="31"></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]01/10/2015[/TD]
[TD]02/10/2015[/TD]
[TD]03/10/2015[/TD]
[TD]04/10/2015[/TD]
[TD]05/10/2015[/TD]
[TD]06/10/2015[/TD]
[TD]07/10/2015[/TD]
[TD]08/10/2015[/TD]
[TD]09/10/2015[/TD]
[TD]10/10/2015[/TD]
[TD]11/10/2015[/TD]
[TD]12/10/2015[/TD]
[TD]13/10/2015[/TD]
[TD]14/10/2015[/TD]
[TD]15/10/2015[/TD]
[TD]16/10/2015[/TD]
[TD]17/10/2015[/TD]
[TD]18/10/2015[/TD]
[TD]19/10/2015[/TD]
[TD]20/10/2015[/TD]
[TD]21/10/2015[/TD]
[TD]22/10/2015[/TD]
[TD]23/10/2015[/TD]
[TD]24/10/2015[/TD]
[TD]25/10/2015[/TD]
[TD]26/10/2015[/TD]
[TD]27/10/2015[/TD]
[TD]28/10/2015[/TD]
[TD]29/10/2015[/TD]
[TD]30/10/2015[/TD]
[TD]31/10/2015[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/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] [/TD]
[TD]Total[/TD]
[TD] [/TD]
[TD]Fcst[/TD]
[TD] [/TD]
[TD]229[/TD]
[TD]248[/TD]
[TD]244[/TD]
[TD]154[/TD]
[TD]206[/TD]
[TD]241[/TD]
[TD]128[/TD]
[TD]232[/TD]
[TD]248[/TD]
[TD]238[/TD]
[TD]241[/TD]
[TD]220[/TD]
[TD]248[/TD]
[TD]114[/TD]
[TD]235[/TD]
[TD]247[/TD]
[TD]244[/TD]
[TD]248[/TD]
[TD]247[/TD]
[TD]239[/TD]
[TD]89[/TD]
[TD]246[/TD]
[TD]245[/TD]
[TD]230[/TD]
[TD]223[/TD]
[TD]245[/TD]
[TD]244[/TD]
[TD]109[/TD]
[TD]248[/TD]
[TD] [/TD]
[TD]6404[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Many thanks,

Richard
 
Sort by date Sort by votes
Have you tried a SUMIFS? Put the start date in one cell, let's say D1, the end date in Cell D2:
=SUMIFS(C1:C32,A1:A32,">="&F2,A1:A32,"<="&F3)

Alternatively, you can use a pivot table and group the dates by Days, select 7 days and select starting date as the first Friday of the month, 02/10/15 in your case. Have a look at the workbook I created here:
https://www.dropbox.com/s/p4ayf422ur4scpx/Date example.xlsx?dl=0
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Formula Filled Dates
Replies
4
Views
506
Fluff
Fluff
K
  • Solved
Populate Dates and Data With Criteria
2
Replies
11
Views
240
Alex Blakenburg
A
K
  • Question Question
Advice on Data Set up to Chart for Ongoing Daily Metrics
Replies
0
Views
191
Kolchak74
K
K
  • Solved
Populate Date With Criteria
Replies
6
Views
154
Peter_SSs
Peter_SSs
JenniferMurphy
  • Solved
Adding a new time causes wrong average
Replies
3
Views
304
awoohaw
awoohaw

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