Forum Discussion
Dropdown list for Week of
Hello everyone,
I am needing help with 2 things.
1. I am wanting to create a dropdown list that will have each Monday in 2021 listed but that Monday represents that entire week (Monday - Sunday). For example: Jan 4 will represent Jan 4-10, Jan 11 will represent Jan 11-17, etc.
2. I have a table that will count the number of occurrences that a specific shift submits a set of data. There are 3 shifts and I want to know how many times each shift submits an audit each week, month, and year. I want the totals to change based on what is selected in the dropdown. So if Jan 4 is selected then I want the numbers to date for that week. If I change the date to Jan 11, the data should change as I select that week.
I hope this makes sense and yall can help me. Thank you.
Update: I added a fake dataset.
10 Replies
- Yes thank you! I think I can make it work.
As Mr Petrukio indicated, you might want to use =ISOWEEKNUM() function as opposed to WEEKNUM() function. We are happy to know that you found your solution.
cheers
As per ISO 8601 the first week of 2021 started on 04th Jan 2021.
You must consider =WEEKNUM(A8,21) to retrieve the real week number.If you want a list of all 2021 Mondays you can simply by array formula
=DATE(YEAR(FirstDate),1,-2)-WEEKDAY(DATE(YEAR(FirstDate),1,3))+(ROW(1:52))*7
Or based on first date you just add 7 days
- Good to know Thank you for the info, bookmarked your response for future reference.
- where's the dataset to create that solution? or did you want someone to re-create your scenario + create a solution for it?
- I added a fake dataset so you can get an idea of what Im looking for.
- Why dont you split your doubt in 2 different posts?
Usually its a better way so everybody can contribute and you reach your solution quicker.
