how to convert a week number to a date range

[bleeet]

Hi is it possible in VBA to convert a week number to the date range Monday to Friday that's with in it?

I want to display the range of dates in a cell for example "dec 11 - dec 15" in a cell.

 

[Jan Mach]

Let the given week number is n. The New Year belongs to the 1st week hence you can calculate the date n-1 weeks later. Then you can find what day of week it is, and find dates of Mo and Fr in this week.

 

[bleeet]

Let the given week number is n. The New Year belongs to the 1st week hence you can calculate the date n-1 weeks later. Then you can find what day of week it is, and find dates of Mo and Fr in this week.

can you please provide an example

sorry I don't understand

 

[MickG]

Place the week number in "A1" , Run code for related Dates (Days Mon to Fri) to show in "B1".
NB:- The code works for the current year.

[COLOR=navy]Sub[/COLOR] MG13Dec11
 [COLOR=navy]Dim[/COLOR] dt [COLOR=navy]As[/COLOR] Date
 [COLOR=navy]For[/COLOR] dt = DateSerial(Year(Date), 1, 1) To DateSerial(Year(Date), 12, 31)
    [COLOR=navy]If[/COLOR] WeekdayName(Weekday(dt), False, vbSunday) = "Friday" [COLOR=navy]Then[/COLOR]
        c = c + 1
        [COLOR=navy]If[/COLOR] c = Range("A1").Value [COLOR=navy]Then[/COLOR] Range("B1").Value = DateAdd("d", -4, dt) & " - " & dt
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] dt
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[Dave Patton]

Excel 2010
	A	B	C
1	Sun Jan 1, 2017
2
3	1	Mon Jan 2, 2017	Fri Jan 6, 2017
4	50	Mon Dec 11, 2017	Fri Dec 15, 2017
5	51	Mon Dec 18, 2017	Fri Dec 22, 2017
6	52	Mon Dec 25, 2017	Fri Dec 29, 2017
7	53	Mon Jan 1, 2018	Fri Jan 5, 2018
8
9	Mon Jan 1, 2018
10	1	Mon Jan 1, 2018	Fri Jan 5, 2018
11
1b

Cell Formulas
Range	Formula
B3	=DATE(2017,1,1)+1+(A3-1)*7
B10	=DATE(2018,1,1)+(A10-1)*7
C3	=B3+4

I added this in case someone wanted a solution without VBA.

 

[bleeet]

thanks guys