You have to declare clearly, what is a week for you - it will be very confusing topic otherwise.

It varies country-wise very broadly, how the week is defined. It can start with different days (Monday in Europe and in lot of other countries, Sunday in US (and probably in a bunch of other countries too), and there is a score of countries with another starting day. Also not every country has same weekend days, or same number of them. And the system on numbering weeks through year can differ.

Here in Europe we have ISO week numbering system, where every week is 7 days long, the week starts with Monday, and the week belongs to year, to where it's Thursday belongs (i.e. to year with at least 4 weekdays in it). There exists no standard to decide, to which month the week belongs - at least I haven't found it - but I myself count a week to month, to which it's Thursday belongs to. I have a formula (found it on some German site many years ago), which calculates ISO weeknumber for any valid Excel date. So I can calculate the weeknumber for 1st January, then calculate the weekday of 1st January, and then depending on found weeknumber (1 or 52/53) I can calculate any weekday of any week of this year. Usually it is better to create a (hidden) Calendary sheet with a list of all dates in needed time interval, where for every date a week number in format "yyyy.ww" is calculated.

I'm not sure, my formula works p.e. for US. You can cope with sunday as start of week, but once I looked at US week numbering system, and I got the impression, 1st January will always be in 1st week of year. Does this mean, there will be weeks 1, 2, .. days long???!!!