Converting a week number to date

There are probably better ways to do this, but =A1*7+DATE(A2,1,1) returns the monday of the week...

In a4 put the formula
=A1*7+DATE(A2,1,1)
and format the cell to a date format you like. This formula always returns the Monday of the week in question (you can check it with the WEEKDAY function), so in a5 you can put the formula
=a4+1
and copy down to a8.

Hope that helps Hello, Could someone please help me with the following little problem ? Is it possible to calculate date's from a weeknummer and a year entered by a user ? When a user enters this I would Excel like to generate the dates for Monday to Friday. Something like this : 27 (Weeknumber entered in cell A1 by user) 2001 (Year entered in cell A2 by user) July 2, 2001 (Date for Monday in cell A4 generated by Excel) July 3, 2001 (Date for Tuesday in cell A5 generated by Excel) July 4, 2001 (Date for Wednesday in cell A6 generated by Excel) July 5, 2001 (Date for Thursday in cell A7 generated by Excel) July 6, 2001 (Date for Friday in cell A8 generated by Excel) Could anyone please tell me if this is possible ? Thanks ! :-) Frits Jager

Re: There are probably better ways to do this, but =A1*7+DATE(A2,1,1) returns the monday of the week...

Careful: this formula only returns Monday in 2001 (because 1 January was Monday)

In a4 put the formula =A1*7+DATE(A2,1,1) and format the cell to a date format you like. This formula always returns the Monday of the week in question (you can check it with the WEEKDAY function), so in a5 you can put the formula =a4+1 and copy down to a8. Hope that helps : : Hello, : : Could someone please help me with the following little : problem ? : Is it possible to calculate date's from a weeknummer and a year entered by a user ? : When a user enters this I would Excel like to generate : the dates for Monday to Friday. : Something like this

Hello,

Could someone please help me with the following : little problem ? Is it possible to calculate date's from a weeknummer and a year entered by a user ?

Thank you for your help, I will give it a try. :-)

Frits Jager

Frits

Try using this code in the Sheet3 (right click on the sheet tab and select
View Code, then paste this in)
If you then enter a week number in A1 and Year in A2, the dates will appear.

Just one thing, though, it misses the first week in January 2001 bacause the first day is a Monday - but you can play around
with it if you like.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Worksheets("Sheet3")
If (Not Application.Intersect(Target, .[A1]) Is Nothing And .[A2] <> "") Or _
(Not Application.Intersect(Target, .[A2]) Is Nothing And .[A1] <> "") Then
myYear = .[A2]
mywk = .[A1]
Dim n As Integer
Dim myMonth As Integer
n = mywk * 7
Select Case n
Case 0 To 31
myDay = n
myMonth = 1
Case 32 To 59
myDay = n - 31
myMonth = 2
Case 60 To 90
myDay = n - 59
myMonth = 3
Case 91 To 120
myDay = n - 90
myMonth = 4
Case 121 To 151
myDay = n - 120
myMonth = 5
Case 152 To 181
myDay = n - 151
myMonth = 6
Case 182 To 212
myDay = n - 181
myMonth = 7
Case 213 To 243
myDay = n - 212
myMonth = 8
Case 244 To 273
myDay = n - 243
myMonth = 9
Case 274 To 304
myDay = n - 273
myMonth = 10
Case 305 To 334
myDay = n - 304
myMonth = 11
Case 335 To 365
myDay = n - 334
myMonth = 12
Case Else
MsgBox "Week number error"
Exit Sub
End Select
Dim myWeekD As Date
myWeekD = myDay & "/" & myMonth & "/" & myYear
wDay = WeekDay(myWeekD)
Select Case wDay
Case 7
.[A4] = myWeekD - 5
.[A5] = myWeekD - 4
.[A6] = myWeekD - 3
.[A7] = myWeekD - 2
.[A8] = myWeekD - 1
Case 6
.[A4] = myWeekD - 4
.[A5] = myWeekD - 3
.[A6] = myWeekD - 2
.[A7] = myWeekD - 1
.[A8] = myWeekD
Case 5
.[A4] = myWeekD - 3
.[A5] = myWeekD - 2
.[A6] = myWeekD - 1
.[A7] = myWeekD
.[A8] = myWeekD + 1
Case 4
.[A4] = myWeekD - 2
.[A5] = myWeekD - 1
.[A6] = myWeekD
.[A7] = myWeekD + 1
.[A8] = myWeekD + 2
Case 3
.[A4] = myWeekD - 1
.[A5] = myWeekD
.[A6] = myWeekD + 1
.[A7] = myWeekD + 2
.[A8] = myWeekD + 3
Case 2
.[A4] = myWeekD
.[A5] = myWeekD + 1
.[A6] = myWeekD + 2
.[A7] = myWeekD + 3
.[A8] = myWeekD + 4
Case 1
.[A4] = myWeekD + 1
.[A5] = myWeekD + 2
.[A6] = myWeekD + 3
.[A7] = myWeekD + 4
.[A8] = myWeekD + 5
Case Else
MsgBox "Cannot compute"
Exit Sub
End Select

Else
End If
End With
End Sub

Any help to you?

Regards

Hello, Could someone please help me with the following little problem ? Is it possible to calculate date's from a weeknummer and a year entered by a user ? When a user enters this I would Excel like to generate the dates for Monday to Friday. Something like this : 27 (Weeknumber entered in cell A1 by user) 2001 (Year entered in cell A2 by user) July 2, 2001 (Date for Monday in cell A4 generated by Excel) July 3, 2001 (Date for Tuesday in cell A5 generated by Excel) July 4, 2001 (Date for Wednesday in cell A6 generated by Excel) July 5, 2001 (Date for Thursday in cell A7 generated by Excel) July 6, 2001 (Date for Friday in cell A8 generated by Excel) Could anyone please tell me if this is possible ? Thanks ! :-) Frits Jager

Frits,

In A4 enter: =DATE(A2,ROUND(A1/4,0),1)+IF(2<WEEKDAY(DATE(A2,ROUND(A1/4,0),1)),7-WEEKDAY(DATE(A2,ROUND(A1/4,0),1))+2,2-WEEKDAY(DATE(A2,ROUND(A1/4,0),1)))
In A5 enter: =A4+1 [copy down this as far as needed.

Caveat: I didn't test the mega-formula, although I expect it to work.

Aladin

========= Hello,

Repost because of that misbehaving web script

Frits,

In A4 enter: =DATE(A2,ROUND(A1/4,0),1)+IF(2 < WEEKDAY(DATE(A2,ROUND(A1/4,0),1)),7-WEEKDAY(DATE(A2,ROUND(A1/4,0),1))+2,2-WEEKDAY(DATE(A2,ROUND(A1/4,0),1)))
In A5 enter: =A4+1 [copy down this as far as needed.

Caveat: I didn't test the mega-formula, although I expect it to work.

Aladin

========= Hello,

Thanks for catching that Robb! (NT)

: =A1*7+DATE(A2,1,1) : and format the cell to a date format you like. This formula always returns the Monday of the week in question (you can check it with the WEEKDAY function), so in a5 you can put the formula : =a4+1 : and copy down to a8. : Hope that helps

One more offering

How about:
=DATE(A2,1,1)+((A1-1)*7)-WEEKDAY(DATE(A2,1,1)+(A1-1)*7,2)+1

Good luck.

Hello, Could someone please help me with the following little problem ? Is it possible to calculate date's from a weeknummer and a year entered by a user ? When a user enters this I would Excel like to generate the dates for Monday to Friday. Something like this : 27 (Weeknumber entered in cell A1 by user) 2001 (Year entered in cell A2 by user) July 2, 2001 (Date for Monday in cell A4 generated by Excel) July 3, 2001 (Date for Tuesday in cell A5 generated by Excel) July 4, 2001 (Date for Wednesday in cell A6 generated by Excel) July 5, 2001 (Date for Thursday in cell A7 generated by Excel) July 6, 2001 (Date for Friday in cell A8 generated by Excel) Could anyone please tell me if this is possible ? Thanks ! :-) Frits Jager

Hello,

I would like to thank anyone involved, for their help.
The "Date" problem has already been solved.
Thanks !!

F. Jager