# Calculate Week Range from Week Number, Month and Year?

1. ## Calculate Week Range from Week Number, Month and Year?

I'm racking my brain trying to figure out a formula I could use to display the Week Range (Ex. 01/05/16 - 07/05/16) given the (Week Number (1,2,3,4,5 etc..), the month and year. Can anyone help me with this?   Register To Reply

2. ## Re: Calculate Week Range from Week Number, Month and Year?

weeknum should give you want you want

https://exceljet.net/formula/get-week-number-from-date

but would also depend on what you classify as first week and also what day of the week you classify as the first day of the week
ie is the 1st of Jan this year considered week 1 even though it started on Friday or week 53 of last year?
making 1st week of this year start of 3rd

^this is also assuming your week starts on a sunday  Register To Reply

3. ## Re: Calculate Week Range from Week Number, Month and Year?

Attached is a workbook with a formula in E2 that takes the week number (within a month), month and year and determines the date range (ending on Saturday) for the week number specified.

The formula doesn't recognize the number of days in a month, so the week range for the 4th or 5th week in a month may show a date that is past the number of days in a month. To get those specifically defined, I think you would need some VBA.

Hope this provides some direction,

Dan  Register To Reply

4. ## Re: Calculate Week Range from Week Number, Month and Year?

Thank you so much for the replies!

But I dont think I was clear enough with my question.

So lets say in a cell I have the month, in another cell I have the year and in another cell I have the week number, how can I calculate the date range based on this information?

When I say weeknumber, I mean specific to the month, so week 1 is the first week of the month listed above, week 2 is the second week of the month listed above, etc... not the week number for the year.

Does this make sense? :x  Register To Reply

5. ## Re: Calculate Week Range from Week Number, Month and Year?

pls attach the sample file  Register To Reply

6. ## Re: Calculate Week Range from Week Number, Month and Year?

I dont have a sample file. I dont even know how to start the code   Register To Reply

7. ## Re: Calculate Week Range from Week Number, Month and Year?

Put week number in cell B1, Month in cell B2, and year in B3.

For result use this formula- ``Please Login or Register  to view this content.``
Check attached for example
excelforum-163.xlsx  Register To Reply

8. ## Re: Calculate Week Range from Week Number, Month and Year?

Thats perfect!!!

How do I make it though so that the first week of the month is the week where the first of the month is in? (So the first of the month, that sun-sat week is the first of the week)

So for example, the first week of June 2016 would display May 29th to June 4th (sun-sat)?  Register To Reply

9. ## Re: Calculate Week Range from Week Number, Month and Year? ``Please Login or Register  to view this content.``
try above formula in "sourabhg98" attached file  Register To Reply

10. ## Re: Calculate Week Range from Week Number, Month and Year? ``Please Login or Register  to view this content.``
try above formula in "sourabhg98" attached file  Register To Reply

11. ## Re: Calculate Week Range from Week Number, Month and Year?

See attached.

Based on 1st of month, column G gives the Sunday of week containing first of month (Week 1).

=F2-WEEKDAY(F2,1)+1

For Week 1

in H2

=TEXT(\$G2+(H\$1-1)*7,"dd/mm/yy") &"-"& TEXT(\$G2+(H\$1-1)*7+6,"dd/mm/yy")

Copy across and down  Register To Reply

12. ## Re: Calculate Week Range from Week Number, Month and Year?

Thank you so much, but Im looking for the exact set up that "sourabhg98" did, and I put in 'nflsales' edit, but it didnt work fully. For example, week 1, month 5 and year 2016 give me the date range as April 24-April 30 which is not the first week of may, its the last week of april   Register To Reply

13. ## Re: Calculate Week Range from Week Number, Month and Year?

See attached...  Register To Reply

14. ## Re: Calculate Week Range from Week Number, Month and Year?

Yes!!! Exactly what Im looking for!! :D
Last question, how do I make it so that the month cell equals whatever number value is in another cell (J3)? It messes up when I put =J3 for example.  Register To Reply

15. ## Re: Calculate Week Range from Week Number, Month and Year?

If you are referring to my table ..

For example, week 1, month 5 and year 2016 give me the date range as April 24-April 30 which is not the first week of may, its the last week of april

Week 1 of May is given as 01/05/2016-07/05/2016.

See attached which uses the table I created.

What do you want (as is the usual case) when Week 5 (or 6) for month n is Week 1 of month n+1?  Register To Reply

16. ## Re: Calculate Week Range from Week Number, Month and Year?

Thank you so so much for all of the help!!!! Amazing experience on here!! :D Thanks Guys!, I got it figured out based on your work :D  Register To Reply

17. ## Re: Calculate Week Range from Week Number, Month and Year?  Register To Reply