How does one collate dates into Monday-Sunday ranges and sum up the data in 2nd Column to those date ranges

[Rhothgar]

I am sure there will be a simple answer to this but I've been trying to research how to do this and have scratched my head for hours.

I know it's not Get Data>Combine Queries>Merge!

I have a dataset? called TSS and a date range (UK format - DD/MM/YYYY) of 01/01/2021 (This was a Friday) - 31/12/2021 (Also a Friday perchance!)

I would like to be able to quickly collate:-

01/01/2021 - 03/01/2021 then,
04/01/2021 - 10/01/2021 then
11/01/2021 - 17/01/2021 and so on and so forth but ending with
20/12/2021 - 26/12/2021 and
27/12/2021 - 31/12/2021

Technically, if this is too difficult I would be able to add in the preceding days before 01/01/2021 to make a full week's data and 01/01/2022 and 02/01/2022 to make a full week at the year end if it makes the job easier.

Then the data in the TSS column would need to sum up for those date ranges so I have the weekly TSS total.

I have created a basic chart. Ultimately, I would then like to compare it to 2022 data on the same graph so I can see whether more work was done in either year on any given week.

I attach a screenshot of part of the data to give a clearer idea of where I am. Obviously the screenshot shows individual days at present in terms of TSS not having been collated. It can even be in a bar chart format. I am not particularly concerned with how it looks if the chart I have chosen cannot handle what I am trying to achieve.

I have absolutely no idea about VBA or coding or whether that would be needed to achieve what I would like to see. I am only a basic Excel user unlike many of you incredibly clever bods!

 

[bebo021999]

How the output look like?
one column with week start, next column is week end, and one column with week sum?

 

[Rhothgar]

How the output look like?
one column with week start, next column is week end, and one column with week sum?

No. I want it to look like the chart in he image but the sum of TSS for each week shown as the cumulative total for that week only.

So about 273 for 04/01 - 10/01 approx. (rough mental calc in my head)

 

[bebo021999]

The chart needs a new source, doesn't it? I were asking about a new source (output)

 

[Rhothgar]

Yes. Probably but I have the daily data. I just selected both columns to create the chart.

Hence the post about how to collate the data into weekly data?

 

[Dave Patton]

One way to create the weekly summary.
You do not have to name the ranges.

SumProduct 2022a.xlsm
	A	B	C	D
1	Dates	TSS
2	01-01-21	58.35	01-01-21	338.69
3	02-01-21	59.22	08-01-21	245
4	03-01-21	91.28	15-01-21	325
5	03-01-21	2.99	22-01-21	0
6	03-01-21	8.63	29-01-21
7	04-01-21	8.63	05-02-21
8	05-01-21	26.67	12-02-21
9	06-01-21	41.23	19-02-21
10	07-01-21	41.69	26-02-21
11	08-01-21	20	05-03-21
12	09-01-21	25	12-03-21
13	10-01-21	30	19-03-21
14	11-01-21	35	26-03-21
15	12-01-21	40	02-04-21
16	13-01-21	45	09-04-21
17	14-01-21	50	16-04-21
18	15-01-21	55	23-04-21
19	16-01-21	60	30-04-21
20	17-01-21	65	07-05-21
21	18-01-21	70	14-05-21
22	19-01-21	75	21-05-21
2b

Cell Formulas
Range	Formula
D2:D5	D2	=SUMPRODUCT(TSS,--(Dates>=C2),--(Dates<C2+7))

Named Ranges
Name	Refers To	Cells
Dates	='2b'!$A$2:$A$22	D2:D5
TSS	='2b'!$B$2:$B$22	D2:D5

 

[Rhothgar]

One way to create the weekly summary.
You do not have to name the ranges.

SumProduct 2022a.xlsm
	A	B	C	D
1	Dates	TSS
2	01-01-21	58.35	01-01-21	338.69
3	02-01-21	59.22	08-01-21	245
4	03-01-21	91.28	15-01-21	325
5	03-01-21	2.99	22-01-21	0
6	03-01-21	8.63	29-01-21
7	04-01-21	8.63	05-02-21
8	05-01-21	26.67	12-02-21
9	06-01-21	41.23	19-02-21
10	07-01-21	41.69	26-02-21
11	08-01-21	20	05-03-21
12	09-01-21	25	12-03-21
13	10-01-21	30	19-03-21
14	11-01-21	35	26-03-21
15	12-01-21	40	02-04-21
16	13-01-21	45	09-04-21
17	14-01-21	50	16-04-21
18	15-01-21	55	23-04-21
19	16-01-21	60	30-04-21
20	17-01-21	65	07-05-21
21	18-01-21	70	14-05-21
22	19-01-21	75	21-05-21
2b

Cell Formulas
Range	Formula
D2:D5	D2	=SUMPRODUCT(TSS,--(Dates>=C2),--(Dates<C2+7))

Named Ranges
Name	Refers To	Cells
Dates	='2b'!$A$2:$A$22	D2:D5
TSS	='2b'!$B$2:$B$22	D2:D5

Hi

Thank you for this. It points me in the right direction.

I have come across an anomaly in the way it handles the information. On 30/01/2021, there are 3 separate entries and I have entered it slightly different to yours to keep the dates shown as days but collating on the Sunday of each week.

Therefore D36 is wrong in my example because of the week containing 9 entries and not 7. I hadn't considered that.

 

[Dave Patton]

N.B. You can post an extract of your sheet with the forum's tool named XL2BB.
Does the following do what you require?

SumProduct 2022a.xlsm
	A	B	C	D
1	Dates	TSS
2	Fri 1-Jan-21	58.35
3	Sat 2-Jan-21	59.22
4	Sun 3-Jan-21	91.28
5	Sun 3-Jan-21	2.99
6	Sun 3-Jan-21	8.63	220.47	220.47
7	Mon 4-Jan-21	8.63
8	Tue 5-Jan-21	26.67
9	Wed 6-Jan-21	41.23
10	Thu 7-Jan-21	41.69
11	Fri 8-Jan-21	20.00
12	Sat 9-Jan-21	25.00
13	Sun 10-Jan-21	30.00	193.22	193.22
14	Mon 11-Jan-21	35.00
15	Tue 12-Jan-21	40.00
16	Wed 13-Jan-21	45.00
17	Thu 14-Jan-21	50.00
18	Fri 15-Jan-21	55.00
19	Sat 16-Jan-21	60.00
20	Sun 17-Jan-21	65.00	350.00	350.00
21	Mon 18-Jan-21	70.00
22	Tue 19-Jan-21	75.00
2b

Cell Formulas
Range	Formula
C2:C22	C2	=IF(AND(WEEKDAY(A2,2)=7,A2<A3),SUMPRODUCT(TSS,--(Dates<=A2),--(Dates>A2-7)),"")
D2:D22	D2	=IF(AND(WEEKDAY(A2,2)=7,A2<A3),SUMIFS(B:B,A:A,"<="&A2,A:A,">="&A2-6),"")

Named Ranges
Name	Refers To	Cells
Dates	='2b'!$A$2:$A$22	C2:D22
TSS	='2b'!$B$2:$B$22	C2:D22

 

[Rhothgar]

That's fantastic. Thank you very much.

Just playing with converting it into a graph of some sort now. Cannot seem to get it into a vertical bar chart but I'll just keep playing. I am sure I will be able to work that out via trial and error.

The formula is total gobbledy-gook to me but what is the difference between your C2 and D2 formulas as they appear achieve the same result? Is it just another way of achieving what C2 does so that either could be used. I do not appear to need both.

Just wondering if there is an advantage to either formula maybe?

I saw this XL2BB add-on last night but haven't installed as yet.

 

[Peter_SSs]

That's fantastic. Thank you very much.

Not sure if it is possible with your data, but if the final date entry is a Sunday I think that you will be missing the final result. See column D below using the column D formula from post #8.
Alternative approach suggested in column C

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

22 07 03.xlsm
	A	B	C	D
1	Dates	TSS
2	Fri, 1-Jan-21	58.35
3	Sat, 2-Jan-21	59.22
4	Sun, 3-Jan-21	91.28
5	Sun, 3-Jan-21	2.99
6	Sun, 3-Jan-21	8.63	220.47	220.47
7	Mon, 4-Jan-21	8.63
8	Tue, 5-Jan-21	26.67
9	Wed, 6-Jan-21	41.23
10	Thu, 7-Jan-21	41.69
11	Fri, 8-Jan-21	20.00
12	Sat, 9-Jan-21	25.00
13	Sun, 10-Jan-21	30.00	193.22	193.22
14	Mon, 11-Jan-21	35.00
15	Tue, 12-Jan-21	40.00
16	Wed, 13-Jan-21	45.00
17	Thu, 14-Jan-21	50.00
18	Fri, 15-Jan-21	55.00
19	Sat, 16-Jan-21	60.00
20	Sun, 17-Jan-21	65.00	350.00
21
22
Sum weeks

Cell Formulas
Range	Formula
C2:C20	C2	=IF(AND(WEEKDAY(A2,2)=7,OR(A2<A3,A3="")),SUM(B$2:B2)-SUM(C$1:C1),"")
D2:D20	D2	=IF(AND(WEEKDAY(A2,2)=7,A2<A3),SUMIFS(B:B,A:A,"<="&A2,A:A,">="&A2-6),"")