Get Date from Week number and Day of the week (1 Viewer)

Ranman256 said:

in a query,
format([date],"ddd") , returns MON ,for day of week
format([date],"ww") , returns 35 , for week in the year

Click to expand...

The_Doc_Man said:

You need one more item to finish this process.

First, you know the week number because that is one of your inputs. Second, you know the day of the week within that week because that is one of your inputs. But THIRD, you need to know the basis of computing the week number.

You see, when you compute a week number, you base that on a starting day of the week for the year. That is because some people want to start the week on the first day of the year even if that day isn't Sunday (or Monday or whatever). So the computation routines that manipulate these dates take into account the starting day of the week.

See these articles:

https://www.techonthenet.com/access/functions/date/format.php

https://msdn.microsoft.com/en-us/library/aa262714(v=vs.60).aspx

Both the FORMAT function and the DATEPART function include the optional starting information for your virtual calendar. Read the articles I referenced if you weren't familiar with these functions.

Format ( expression, [ format, [ firstdayofweek, [firstweekofyear] ] ] )

DatePart(interval, date[,firstdayofweek[, firstweekofyear]])

You also need to know if a value was used for the first week of the year. Again, look at the articles to understand your options.

Now, here is how you compute the date of the targeted weekday for that week of the year.

1. Determine the basis date for starting the week computation. I.e. when you computed the week number, what did you use for that computation? Determine the day of the year for that day of the week. You must realize from looking at the options for the FORMAT or DATEPART functions that the first week of the year MIGHT not even be in the same year if, for example, the starting day is chosen as Sunday and the starting week is chosen as the one that CONTAINS 1 Jan which happens to be a Monday. In that hypothetical case, the first week of the year starts on the Sunday that ended the previous year.

2. Compute a base-week date as DATEADD( "ww", nn-1, basis-date ) - and you need the -1 because if I recall correctly, the week number will be one-based but DATEADD won't be. That is, there is no week 0. The week that contains the selected basis-date for the year is week 1.

3. Now, the tricky part. You know the weekday for the basis date (because it is either Sunday, the default, and day = 1, or you found another value if the default was not used). You already know the day of the week for the desired day, you just don't know the date yet. Those two days will be expressed as numbers from 1 to 7.

Subtract 1 from EACH weekday number (to change them from Sunday = 1 to Sunday = 0; but more technically, to again make the numbers ZERO based, not ONE based.) Subtract the adjusted starting weekday number from the adjusted target weekday number. This is now the difference in days between the two weekdays.

If the result is zero, the weekday computation took you to the right date already. The computed week-base is the date you wanted. You are done.

If the difference is less than zero, add 7. Then (if the difference wasn't zero) compute another DATEADD( "d", difference, weekday-base ).

Let's say your day in question was a Monday but this year your week-count began on a Friday. Friday = day 6. Monday = day 2. So adjusted, equals 5 and 1. Subtract in the order stated and you get -4, which is less than 7, so add 7 and you get 3. So from your weekday base from step 2 earlier, add 3 more days and you get the date of the desired Monday.

Click to expand...

plog said:

Your data assumes you are always calculating the date in the current year. If that's true, I think the method involved is a lot simpler. You simply have to add the difference in your required week and the current week to the current date. Then add the difference in the required day to the current day to that.

If you didn't store the days as text and instead numbers (Sun=1, Mon=2, etc.), this could have been done in 2 lines of code. Here's the code that works for your sample data:

Code:Copy to clipboard

Function get_Date(in_Week, in_Day) As Date
' determines date based on week number (in_Week) and day of week (in_Day) it is passed

ret = DateAdd("ww", in_Week - DatePart("ww", Date), Date)
' sets return value to this day of the week in whatever week requested

int_Day = 1
    ' will hold numeric value that in_Day is (e.g. Sun=1, Mon=2, etc.)
    
Select Case in_Day
    ' converts in_Day to number
    Case "Sun"
        int_Day = 1
    Case "Mon"
        int_Day = 2
    Case "Tue"
        int_Day = 3
    Case "Wed"
        int_Day = 4
    Case "Thu"
        int_Day = 5
    Case "Fri"
        int_Day = 6
    Case "Sat"
        int_Day = 7
End Select


ret = DateAdd("d", int_Day - Weekday(Date), ret)
' moves return value based on how many days after requested day it is now

get_Date = ret
End Function

Paste that into a module, then in a query you use it like so:

DateOfData: get_Date([WeekId], [Day])

Click to expand...

plog said:

Is Monday really the first day of your week? Your example data did not include any Saturday or Sunday data. That makes me believe that you do not expect those dates in your data. If so, then the value for Sunday is irrelevant and I suggest you make Monday=2.

Click to expand...

plog said:

It would be a similar approach to the previous code I posted, however your starting date would be the first of the month. You would start out by using DateValue (https://www.techonthenet.com/access/functions/date/datevalue.php) to set your initial date. You would then add one week to it using DateAdd, and finally use the day method of my original code to accomodate what day of the week you neeeded.

Click to expand...

plog said:

Yes, you do have a starting point, the first of the month. Reread my post, it details the method.[/QUOTE

I don't have the first of the month as I would need to dynamically create it depending on what year I am in. So how do I generate the first of the month using only the month number?

Click to expand...