ashleyfoozi
New Member
- Joined
- Mar 8, 2022
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
Hi All
I have dates in a data set occuring from the beginning of 2021 and we are now in March 2022. Therefore if I use a formula to sum the data occuring in a week number it is going to add Jan 2021 and Jan 2022 together, which is not want I want to show. I only want to show the data from this year.
How can I determine if the week number has not yet occured in a year and must therefore be the previous years week number and must not be included.
EDIT:
The data is added automatically to the data sheet from SharePoint and after naming my ranges the sheet is hidden. My customers used to want the data shown monthly, but now want to see it weekly as in Sheet 2. All formulas in sheet 2 are dynamic and the dates, weeks etc update automatically. The year must do the same.
If I can get the year into Sheet 2 B1, C1 etc then it would be easy to do a SUMIF matching the Week and Year in Sheet 1. As you can see in Sheet 2 the week 51 occured last year 2021, so if I were to add a row at Row 1 and show the years then B1 should be 2021and B2 week 51, C1 should be Year 2021 and C2 week 52, D1 Year 2022 and D2 Week 1 etc.
I have dates in a data set occuring from the beginning of 2021 and we are now in March 2022. Therefore if I use a formula to sum the data occuring in a week number it is going to add Jan 2021 and Jan 2022 together, which is not want I want to show. I only want to show the data from this year.
How can I determine if the week number has not yet occured in a year and must therefore be the previous years week number and must not be included.
| Book1 | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | Date | Week | Year | Count | ||
| 2 | 2022/01/20 | 3 | 2022 | 3 | ||
| 3 | 2022/01/20 | 3 | 2022 | 1 | ||
| 4 | 2022/01/20 | 3 | 2022 | 4 | ||
| 5 | 2021/12/08 | 49 | 2021 | 5 | ||
| 6 | 2021/12/08 | 49 | 2021 | 4 | ||
| 7 | 2021/12/08 | 49 | 2021 | 1 | ||
| 8 | 2021/11/10 | 45 | 2021 | 2 | ||
| 9 | 2021/11/10 | 45 | 2021 | 3 | ||
| 10 | 2021/10/13 | 41 | 2021 | 1 | ||
| 11 | 2021/10/13 | 41 | 2021 | 2 | ||
| 12 | 2021/10/13 | 41 | 2021 | 1 | ||
| 13 | 2021/09/08 | 36 | 2021 | 2 | ||
| 14 | 2021/09/08 | 36 | 2021 | 2 | ||
| 15 | 2021/09/08 | 36 | 2021 | 2 | ||
| 16 | 2021/09/08 | 36 | 2021 | 1 | ||
| 17 | 2021/09/08 | 36 | 2021 | 3 | ||
| 18 | 2021/09/08 | 36 | 2021 | 3 | ||
| 19 | 2021/09/08 | 36 | 2021 | 1 | ||
| 20 | 2021/08/11 | 32 | 2021 | 2 | ||
| 21 | 2021/08/11 | 32 | 2021 | 1 | ||
| 22 | 2021/08/11 | 32 | 2021 | 3 | ||
| 23 | 2021/08/11 | 32 | 2021 | 3 | ||
| 24 | 2021/08/11 | 32 | 2021 | 1 | ||
| 25 | 2021/07/07 | 27 | 2021 | 1 | ||
| 26 | 2021/07/07 | 27 | 2021 | 2 | ||
| 27 | 2021/07/07 | 27 | 2021 | 1 | ||
| 28 | 2021/06/09 | 23 | 2021 | 2 | ||
| 29 | 2021/06/09 | 23 | 2021 | 2 | ||
| 30 | 2021/06/09 | 23 | 2021 | 4 | ||
| 31 | 2021/06/09 | 23 | 2021 | 3 | ||
| 32 | 2021/04/14 | 15 | 2021 | 3 | ||
| 33 | 2021/04/14 | 15 | 2021 | 4 | ||
| 34 | 2021/04/14 | 15 | 2021 | 5 | ||
| 35 | 2021/03/11 | 10 | 2021 | 4 | ||
| 36 | 2021/03/11 | 10 | 2021 | 4 | ||
| 37 | 2021/03/11 | 10 | 2021 | 3 | ||
Sheet1 | ||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2:B37 | B2 | =ISOWEEKNUM('Weet-Bix.xlsx'!Table_query__10[@Date]) |
| C2:C37 | C2 | =YEAR('Weet-Bix.xlsx'!Table_query__10[@Date]) |
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A1 | A1 | =YEAR(TODAY()) |
| B1 | B1 | =ISOWEEKNUM(TODAY()-77) |
| C1 | C1 | =ISOWEEKNUM(TODAY()-70) |
| D1 | D1 | =ISOWEEKNUM(TODAY()-63) |
| E1 | E1 | =ISOWEEKNUM(TODAY()-56) |
| F1 | F1 | =ISOWEEKNUM(TODAY()-49) |
| G1 | G1 | =ISOWEEKNUM(TODAY()-42) |
| H1 | H1 | =ISOWEEKNUM(TODAY()-35) |
| I1 | I1 | =ISOWEEKNUM(TODAY()-28) |
| J1 | J1 | =ISOWEEKNUM(TODAY()-21) |
| K1 | K1 | =ISOWEEKNUM(TODAY()-14) |
| L1 | L1 | =ISOWEEKNUM(TODAY()-7) |
| M1 | M1 | =ISOWEEKNUM(TODAY()) |
| B2:M2 | B2 | =CHOOSE(MONTH(DATE($A$4,1,B1*7-2)-WEEKDAY(DATE(B1,1,3))),"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec") |
| B3:M3 | B3 | =CONCATENATE("WEEK"," ",B1) |
| B4:M13 | B4 | =SUMIFS(MMCount,MMWeek,B$4,MMNo,$A4) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| L3:M3 | Cell Value | ="OK" | text | NO |
| L3:M3 | Cell Value | ="ACTIVITY" | text | NO |
| B4:M13 | Cell Value | ="NYC" | text | NO |
| B4:M13 | Cell Value | >0 | text | NO |
| B4:M13 | Cell Value | =0 | text | NO |
EDIT:
The data is added automatically to the data sheet from SharePoint and after naming my ranges the sheet is hidden. My customers used to want the data shown monthly, but now want to see it weekly as in Sheet 2. All formulas in sheet 2 are dynamic and the dates, weeks etc update automatically. The year must do the same.
If I can get the year into Sheet 2 B1, C1 etc then it would be easy to do a SUMIF matching the Week and Year in Sheet 1. As you can see in Sheet 2 the week 51 occured last year 2021, so if I were to add a row at Row 1 and show the years then B1 should be 2021and B2 week 51, C1 should be Year 2021 and C2 week 52, D1 Year 2022 and D2 Week 1 etc.
Attachments
Last edited by a moderator:
