Unlucky
Board Regular
- Joined
- Dec 3, 2014
- Messages
- 58
- Office Version
- 2016
- Platform
- Windows
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.
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) |