Return a Date for a specific week

[bobgrand]

Hello all,

We receive date data in a weird format YYWK. WK represents the Week of the year. Is there a formula that will return a date for the work week of the year.
Example: 1824 - 18 = 2018 - 24 = Work Week 24 of the year would be Monday 06/18/2018.

Any help would be greatly appreciated.

Bob

 

[njimack]

Based on your example, try the following:
=DATE(2000+LEFT(A1,2),1,MID(A1,3,2)*7)+1

 

[bobgrand]

Neil,

Thank you so much. That works perfect.

Unfortunately that was only half the battle. I now need the reverse of this. I have a date and need to know which week of the year that is. If you can help with this that would be awesome.

Thank you
Bob

 

[Legacy 421546]

'***************************************************************************************************
'* ISO Week Number
'* International Standards Organization (ISO)
'* The ISO 8601 definition for week 01 is the week with the year's first Thursday in it
'* ISO definition: [URL]http://en.wikipedia.org/wiki/ISO_week_date[/URL]
'* Code: [URL]http://www.cpearson.com/excel/WeekNumbers.aspx[/URL]
'*
Function WeekNum_ISO(InputDate As Date) As Integer
    WeekNum_ISO = 0
    If InputDate <> 0 Then  'Returns week 52 for date = 0
        WeekNum_ISO = DatePart("ww", InputDate, vbMonday, vbFirstFourDays)
        If WeekNum_ISO = 53 And day(InputDate) > 28 And Weekday(InputDate) = vbMonday Then WeekNum_ISO = 1  'Bugfix 2019
    End If
End Function

 

[bobgrand]

Thank you Johnny, I will give this a try but for the time being I created a separate tab ColA 1 thru 52 ColB Date 1/8/2018 + 7 carried down to give me the first Monday date of each week. I will v-lookup against this to get the dates into the sheet.

1	1/8/2018
2	1/15/2018
3	1/22/2018
4	1/29/2018
5	2/5/2018
6	2/12/2018
7	2/19/2018
8	2/26/2018
9	3/5/2018
10	3/12/2018

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[njimack]

Excel Workbook
	A	B	C
1	1824	18/06/2018	24
Sheet1