How to tell access that date is a week/year

Question

How do I tell an access query that the date I have in the cells are weeks/year? ex. 5/2015 is week 5 in 2015.

I have tried format([TheDate]; "ww/yyyy") but it then converts 5/2015 to the first week of may in 2015 (20/2015)

It is only a problem with weeks below 12, as they will pass rightfully through when it is week 13-52, as there are only 12 months :P

Answer 1

You will need first a function that resolves the week-year to a date of that week. This is not that easy, as some weeks cross calendar year boundaries, and VBA doesn't calculate week 53 correctly for some years:

Public Function ISO_DateOfWeek( _
    ByVal intYear As Integer, _
    ByVal bytWeek As Byte, _
    Optional ByVal bytWeekday As Byte = vbMonday) _
    As Date

' Calculates date of requested weekday in a week of
' a year according to ISO 8601:1988 standard.
'
' Notes:  Years less than 100 will be handled as
'         two-digit years of our current year frame.
'         Years less than zero returns a zero date.
'         A weeknumber of zero returns the requested
'         weekday of the week before week 1.
'
' 2000-12-17. Cactus Data ApS, Gustav Brock.

    ' The fourth of January is always included in
    ' the first week of year intYear.
    Const cbytDayOfFirstWeek  As Byte = 4
    ' Number of days in a week.
    Const cbytDaysOfWeek      As Byte = 7
    ' Month of January.
    Const cbytJanuary         As Byte = 1

    Dim datDateOfFirstWeek    As Date
    Dim intISOMonday          As Integer
    Dim intISOWeekday         As Integer
    Dim intWeekdayOffset      As Integer

    ' No specific error handling.
    On Error Resume Next

    If intYear > 0 Then
        ' Weekday of Monday.
        intISOMonday = Weekday(vbMonday, vbMonday)
        ' Date of fourth of January in year intYear.
        datDateOfFirstWeek = DateSerial(intYear, cbytJanuary, cbytDayOfFirstWeek)
        ' Weekday of fourth of January in year intYear.
        intISOWeekday = Weekday(datDateOfFirstWeek, vbMonday)
        ' Calculate offset from Monday in first week of year intYear.
        intWeekdayOffset = intISOMonday - intISOWeekday

        ' Weekday of requested weekday.
        intISOWeekday = Weekday(bytWeekday, vbMonday)
        ' Calculate offset from requested weekday in first week of year intYear.
        intWeekdayOffset = intWeekdayOffset + intISOWeekday - intISOMonday
        ' Date of requested weekday in first week of year intYear.
        datDateOfFirstWeek = DateAdd("d", intWeekdayOffset, datDateOfFirstWeek)

        ' Date of requested weekday in requested week of year intYear.
        datDateOfFirstWeek = DateAdd("ww", bytWeek - 1, datDateOfFirstWeek)
    End If

    ISO_DateOfWeek = datDateOfFirstWeek

End Function

then a function to format this correctly:

Public Function ISO_WeekYearNumber( _
    ByVal datDate As Date, _
    Optional ByRef intYear As Integer, _
    Optional ByRef bytWeek As Byte) _
    As String

' Calculates and returns year and week number for date datDate according to the ISO 8601:1988 standard.
' Optionally returns numeric year and week.
' 1998-2007, Gustav Brock, Cactus Data ApS, CPH.
' May be freely used and distributed.

    Const cbytFirstWeekOfAnyYear  As Byte = 1
    Const cbytLastWeekOfLeapYear  As Byte = 53
    Const cbytMonthJanuary        As Byte = 1
    Const cbytMonthDecember       As Byte = 12
    Const cstrSeparatorYearWeek   As String = "W"

    Dim bytMonth                  As Byte
    Dim bytISOThursday            As Byte
    Dim datLastDayOfYear          As Date

    intYear = Year(datDate)
    bytMonth = Month(datDate)
    bytWeek = DatePart("ww", datDate, vbMonday, vbFirstFourDays)

    If bytWeek = cbytLastWeekOfLeapYear Then
        bytISOThursday = Weekday(vbThursday, vbMonday)
        datLastDayOfYear = DateSerial(intYear, cbytMonthDecember, 31)
        If Weekday(datLastDayOfYear, vbMonday) >= bytISOThursday Then
            ' OK, week count of 53 is caused by leap year.
        Else
            ' Correct for Access97/2000+ bug.
            bytWeek = cbytFirstWeekOfAnyYear
        End If
    End If

    ' Adjust year where week number belongs to next or previous year.
    If bytMonth = cbytMonthJanuary Then
        If bytWeek >= cbytLastWeekOfLeapYear - 1 Then
            ' This is an early date of January belonging to the last week of the previous year.
            intYear = intYear - 1
        End If
    ElseIf bytMonth = cbytMonthDecember Then
        If bytWeek = cbytFirstWeekOfAnyYear Then
            ' This is a late date of December belonging to the first week of the next year.
            intYear = intYear + 1
        End If
    End If

    ISO_WeekYearNumber = CStr(intYear) & cstrSeparatorYearWeek & Format(bytWeek, "00")

End Function

Of course, you can modify the last function to provide the desired string.

Answer 2

I have tried format([TheDate]; "ww/yyyy") but it then converts 5/2015 to the first week of may in 2015 (20/2015)

The reason this is happening is that you are passing a date into the Format() function. The date you are passing in is 5/2015 which Access believes is May of 2015 (probably May 1st). The format function then formats that date to your specified format of "ww/yyyy" which is, correctly, 20/2015.

This is just Access being Access. Making assumptions about your data instead of throwing an error or making you define things implicitly.

There may be ways to do this in a formula. Really ugly ways. Instead you could solve it with a UDF:

Function Week2Date(weekyear As String) As Date
    Dim arrWeekYear As Variant
    Dim dateJan1 As Date
    Dim Sub1 As Boolean

    'Split the incoming weekyear by "/" to an array
    arrWeekYear = Split(weekyear, "/")

    'determine the first of the year
    dateJan1 = DateSerial(arrWeekYear(1), 1, 1)

    'Add the weeks to the date and dance a little jig
    Sub1 = (Format(dateJan1, "ww", vbUseSystem, vbUseSystem) = 1)
    Week2Date = DateAdd("ww", arrWeekYear(0) + Sub1, dateJan1)
    Week2Date = Week2Date - Weekday(Week2Date) + 7


End Function

This will take the text date format you have "ww/yyyy" and convert it into an actual date. Since you have to make some assumptions to convert a 7 day period into a single date, this should get you close but you might have to tweak it a bit to get the exact date you want out of it (Like... perhaps you only want to start counting at the first full week and then use the Monday of each week as the date returned).