SORT DATE INTO WEEK

[Unlucky]

I reviewed previous posts for determining weeks by date, but instead of a running count of months, I separated each month into 5 weeks Specifically,

Day 1 thru 7 is Week 1
Day 8 thru 14 is Week 2
Day 15 thru 21 is Week 3
Day 22 thru 28 is Week 4
Day 29 thru 31 is Week 5

It is understood that some months have only 30 days (and at most February on has 29.

Bottom Line:
I need to sort the data by month and then week, and then by program. The breakout is necessary for monthly product sales and determining which week has a peak in sales. Here is the rough draft table I'm working on.

AUDIT METRICS.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
8	PROGRAM AUDIT METRICS (INITIAL, RECHECK, ETC.)							PROGRAM AUDIT METRICS (INITIAL, RECHECK, ETC.)
9
10	A	WEEK	WEEK	WEEK	WEEK	WEEK		B	WEEK	WEEK	WEEK	WEEK	WEEK
11		1	2	3	4	5			1	2	3	4	5
12	JAN	0	0	0	0	0		JAN	0	0	0	0	0
13	FEB	0	0	0	0	0		FEB	0	0	0	0	0
14	MAR	0	0	0	0	0		MAR	0	0	0	0	0
15	APR	0	0	0	0	0		APR	0	0	0	0	0
16	MAY	0	0	0	0	0		MAY	0	0	0	0	0
17	JUN	0	0	0	0	0		JUN	0	0	0	0	0
18	JUL	0	0	0	0	0		JUL	0	0	0	0	0
19	AUG	0	0	0	0	0		AUG	0	0	0	0	0
20	SEP	0	0	0	0	0		SEP	0	0	0	0	0
21	OCT	0	0	0	0	0		OCT	0	0	0	0	0
22	NOV	0	0	0	0	0		NOV	0	0	0	0	0
23	DEC	0	0	0	0	0		DEC	0	0	0	0	0
24	TOTAL	0	0	0	0	0		TOTAL	0	0	0	0	0
25	AVERAGE	0.00	0.00	0.00	0.00	0.00		AVERAGE	0.00	0.00	0.00	0.00	0.00
26
27
28	DATE	WEEK	WEEK No.	PROGRAM		START DATE	END DATE		WK 1 START	WK 2 START	WK 3 START	WK 4 START	WK 5 START
29	7-Jan-2021	1	2	A		1-Jan-2021	31-Jan-2021		1-Jan-2021	8-Jan-2021	15-Jan-2021	22-Jan-2021	29-Jan-2021
30	8-Jan-2021	2	2	A		1-Feb-2021	28-Feb-2021		1-Feb-2021	8-Feb-2021	15-Feb-2021	22-Feb-2021
31	8-Jan-2021	2	2	A		1-Mar-2021	31-Mar-2021		1-Mar-2021	8-Mar-2021	15-Mar-2021	22-Mar-2021	29-Mar-2021
32	9-Jan-2021	2	2	A		1-Apr-2021	30-Apr-2021		1-Apr-2021	8-Apr-2021	15-Apr-2021	22-Apr-2021	29-Apr-2021
33	12-Jan-2021	2	3	A		1-May-2021	31-May-2021		1-May-2021	8-May-2021	15-May-2021	22-May-2021	29-May-2021
34	12-Jan-2021	2	3	B		1-Jun-2021	30-Jun-2021		1-Jun-2021	8-Jun-2021	15-Jun-2021	22-Jun-2021	29-Jun-2021
35	12-Jan-2021	2	3	A		1-Jul-2021	31-Jul-2021		1-Jul-2021	8-Jul-2021	15-Jul-2021	22-Jul-2021	29-Jul-2021
36	12-Jan-2021	2	3	B		1-Aug-2021	1-Aug-2021		1-Aug-2021	8-Aug-2021	15-Aug-2021	22-Aug-2021	29-Aug-2021
37	15-Jan-2021	3	3	B		1-Sep-2021	30-Sep-2021		1-Sep-2021	8-Sep-2021	15-Sep-2021	22-Sep-2021	29-Sep-2021
38	16-Jan-2021	3	3	B		1-Oct-2021	31-Oct-2021		1-Oct-2021	8-Oct-2021	15-Oct-2021	22-Oct-2021	29-Oct-2021
39	30-Jan-2021	5	5	A		1-Nov-2021	30-Nov-2021		1-Nov-2021	8-Nov-2021	15-Nov-2021	22-Nov-2021	29-Nov-2021
40	30-Jan-2021	5	5	A		1-Dec-2021	31-Dec-2021		1-Dec-2021	8-Dec-2021	15-Dec-2021	22-Dec-2021	29-Dec-2021
41	31-Jan-2021	5	6	B
42	3-Feb-2021	1	6	A					MONTHLY BREAKDOWN
43	3-Feb-2021	1	6	B					WEEK 1	WEEK 2	WEEK 3	WEEK 4	WEEK 5
44	3-Feb-2021	1	6	B					1-7	8-14	15-21	22-28	29-31
45	6-Feb-2021	1	6	B
46	7-Feb-2021	1	7	B
47	7-Feb-2021	1	7	B
48	19-Feb-2021	3	8	B
49	20-Feb-2021	3	8	B
50	20-Feb-2021	3	8	B
51	21-Feb-2021	3	9	B
52	22-Feb-2021	4	9	A
53	28-Feb-2021	4	10	A
54	28-Feb-2021	4	10	A
55	28-Feb-2021	4	10	A
56	28-Feb-2021	4	10	B
57	28-Feb-2021	4	10	A
58	1-May-2021	1	18	B
59	3-Mar-2021	1	10	B
60	4-Mar-2021	1	10	B
61	4-Mar-2021	1	10	B
62	6-Mar-2021	1	10	A
63	7-Mar-2021	1	11	A
64	7-Mar-2021	1	11	A
65	8-Mar-2021	2	11	B
66	9-Mar-2021	2	11	A
67	10-Mar-2021	2	11	B
68	10-Mar-2021	2	11	A
69	10-Mar-2021	2	11	B
70	11-Mar-2021	2	11	A
71	12-Mar-2021	2	11	B
72	16-Mar-2021	3	12	B
73	17-Mar-2021	3	12	B
74	21-Mar-2021	3	13	A
75	21-Mar-2021	3	13	A
76	26-Mar-2021	4	13	A
77	26-Mar-2021	4	13	B
78	31-Mar-2021	5	14	A
FORMULA TEST

Cell Formulas
Range	Formula
J24:N24,C24:G24	C24	=SUM(C12:C23)
J25:N25,C25:G25	C25	=AVERAGEIF(C12:C23,">=0")
D29:D78	D29	=WEEKNUM(B29,1)

 

[queuesmef]

for month, use formula =Month() & pull from column A; [ use month function]
for week,

=IF(DAY(A1)<8,1,IF(DAY(A1)<15,2,IF(DAY(A1)<22,3,IF(DAY(A1)<29,4,IF(DAY(A1)<32,5,"Error")))))

this will give a week number. [use day function]
I would then put this info into a Pivot table to summarize and standardize the layout.
let me know if this helps you, or gets you going in the right direction?

 

[Unlucky]

When I entered the formula, in cells D29:D78 it reported 1 in every cell. I got the same result for C29:C78. Same result if I use the Ctrl+Shift+Enter.

 

[Fluff]

I suggest that you update your Account details (or 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’)

How about

=INT((DAY(B29)-1)/7)+1

 

[Unlucky]

I suggest that you update your Account details (or 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’)

How about

=INT((DAY(B29)-1)/7)+1

I apologize. I updated my profile to include Excel 2016.

 

[Unlucky]

I suggest that you update your Account details (or 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’)

How about

=INT((DAY(B29)-1)/7)+1

This worked like a charm. Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.

I updated my profile to include Excel 2016.

Thanks for that as well.

 

[queuesmef]

When I entered the formula, in cells D29:D78 it reported 1 in every cell. I got the same result for C29:C78. Same result if I use the Ctrl+Shift+Enter.

if you mean on my version, change A1 to be the same row as where your putting in formula. EX if in row 12, change all the 'A1' to 'A12'.

 

[Fluff]

@queuesmef what's in col A?

 

[Unlucky]

@queuesmef what's in col A?

I actually use a separate spreadsheet to test formulas and make sure they work prior to incorporating them into the main workbook. I figured out what the problem was . . . . . I use the $ in the wrong place when I dragged the cells to complete the table. No worries, its all good now. Thanks again!