Only year-week used in data file - Best practice

[Frenky]

Hi all,

I've been checking a heaps of links/videos but due to an overload of (new) information I struggle to get it all working properly.

Our data is received on week level - So the 3 options available are YEAR-WEEK / YEAR / WEEK

My aim is to also show the months and quarters.
I did create a date table but there is no match since I cannot convert one of the 3 options into a date.
When I try convert i.e. the year 2019 into a date it shows me 12th of july 1905.

What is the best way to get this solved?
Working from a date table --> Data or the other way around and adjust the data and add a column with a specific date to the year-week data?

Sorry if this is a bit of a noob question - already learned that it's important so want to do it well.

Thanks,

 

[ranman256]

use this function to convert the julian to normal date, usage: cvtJulian2Date("21054")

Public Function cvtJulian2Date(ByVal pvJD)
Dim dt As Date
Dim jd As Long
cvtJulian2Date = DateSerial(2000 + Int(pvJD / 1000), 1, pvJD Mod 1000)
End Function

 

[ranman256]

convert week# to date
usage: getDayFromWeekNumber(year, weeknum, dowStart)

Public Function GetDayFromWeekNumber(InYear As Integer, WeekNumber As Integer, Optional DayInWeek1Monday7Sunday As Integer = 1) As Date
    Dim i As Integer: i = 1
    If DayInWeek1Monday7Sunday < 1 Or DayInWeek1Monday7Sunday > 7 Then
        MsgBox "Please input between 1 and 7 for the argument :" & vbCrLf & _
                "DayInWeek1Monday7Sunday!", vbOKOnly + vbCritical
        'Function will return 30/12/1899 if you don't use a good DayInWeek1Monday7Sunday
        Exit Function
    Else
    End If
    Do While Weekday(DateSerial(InYear, 1, i), vbMonday) <> DayInWeek1Monday7Sunday
        i = i + 1
    Loop
    GetDayFromWeekNumber = DateAdd("ww", WeekNumber - 1, DateSerial(InYear, 1, i))
End Function