# 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?

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

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

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

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

pls attach the sample file

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

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

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)?

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

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

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

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

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

See attached...

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.

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?

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

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1