Receive a date range if I have week number and year

In Cell F1 I have March 2015. In cell F2 I have the week number of that specific month. In cell F4 I would like to return the date range, ex. (March 9, 2015 - March 13, 2015.) It is important to tie to cells F1 and F2, as I have data validation drop boxes on the month and week that will correspond with the rest of the data. I have read of doing this if you have the week number of the year, but I need to be able to do this with the week number of the month (Monday to Friday.) Is this possible?

I have this formula in place on my data sheet to calculate the week number of the month:

=WEEKNUM(B2,2)-WEEKNUM(DATE(YEAR(B2),MONTH(B2),1),2)

This formula will return a number 0-5. "0" is returned for an instance such as and entry January 1st or 2nd. I add plus 1 to the end of the formula to push those values to the next week (week 1). Same goes for an instance such as March 30th and 31st. All other instances fall under a full Monday to Friday. For instances where I add or minus one from the above formula, I would need the returned date range to include those dates. So if I ran this report on January 9, the week would be considered week 1 and the returned values would be "January 1, 2015 - January 9, 2015. For an instance such as the end of March, I would want "March 30, 2015 - April 3, 2015" The all other weeks would be the Monday to Friday date.

I hope this helps! Im pretty good in Excel but this one is driving me crazy. Any help is appreciated

to clarify- For March 30 and 31st I actually subtract 4 to pull it into week one of the next month. I have attached the file for your reference. Any help or direction is appreciated!

Not sure if this will work for you, but try this:

=MAX(DATE(F1,1,1),DATE(F1,1,1)-WEEKDAY(DATE(F1,1,1),2)+(F2-1)*7+1)

Cell F1 should be only the YEAR,
Cell F2 should be the Week Number

So if F1 = 2015
and if F2 = 12 ( Week Number 12 )
then you should get the Date of: Monday, March 16, 2015

Thank you but this formula would only work with the week number of the year. I need it to work for the week number of the month..

Please find the file attach, hoping the best

see you

Thank you Azumi, this is the closest ive got to a solution, but this formula works for only a few months. if you change the date to april 2015, it the formula falls apart

so when the box reads april 2015 week 1, i would need the dates to return March 30, 2015 - April 3, 2015...formula also breaks for january 2015....

Thank you Azumi but still a little off...week 1 for march 2015 pulls february dates..i feel like its so close please help me!

Seems correct now, Im tes with random number of month and weeks, this my last try...

You sir are brilliant. Thank you very much.

Ahhh... glad it works

Hi Azumi, I hope all is well. I was hopin gfor some help on a formula if you have time. If you reference the sheet you attached on this thread on 3/22, I want to do the opposite. Rather then plug a week number in cell B2 to return the date range, I want to plug just a date,(not a date range) in and have the formula spit out the week number of the month. Can you please help? Here is what I came up wth but it breaks for date like 3/30, and 3/31...

=IF(WEEKNUM(B2,2)-WEEKNUM(DATE(YEAR(B2),MONTH(B2),1),2)=0,1,IF(WEEKNUM(B2,2)-WEEKNUM(DATE(YEAR(B2),MONTH(B2),1),2)=5,1,(WEEKNUM(B2,2)-WEEKNUM(DATE(YEAR(B2),MONTH(B2),1),2))))

Im confused what you after now, let me clarify you have date on both cells A2 and B2, and what the results? The week number?

I have 1 date, I need week number of that month for that specifc date. So in your spreadsheet, if the date I have fell in between the dates ranges in A9 and B9, the returned value would be the month and week number of month...(April20151)...Month is April, 2015 is year, 1 is week number

Thank yuou for the response..Not quite what I am looking for. This looks like its on the right track, but it breaks for dates like january 27 or march 31. It returns a 5. I would need a date like march 31st to be considered as part of week 1 for april. any ideas?