Dropdown list for Week of

[catherine9910]

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. 

[#2]

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.

[#3]

where's the dataset to create that solution? or did you want someone to re-create your scenario + create a solution for it?

[#4]

I added a fake dataset so you can get an idea of what Im looking for.

[#5]

@catherine9910 

so were you looking for something like this? see attached

[#6]

@Yea_So 

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

[#7]

Good to know Thank you for the info, bookmarked your response for future reference.

[#8]

Anytime my friend.
Actually Microsoft has solved this "issue" creating for the new excel versions the formula
=ISOWEEKNUM(Date).
As I use to provide a more "democratic" solution that can be applied for many excel versions, I forgot to mention the new formula.

[#9]

Good to know thank you

[#10]

Yes thank you! I think I can make it work.

[#11]

@catherine9910 

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