Format - Convert a weeknumber+weekday to a proper date format

[aubrey1983]

Hi,
I have a query, that contains the field, weeknumber and weekday value, and year.

now i want to convert this values to proper/standard date format.

Example:
Weeknumber: 19
Weekday: 3 (Tuesday)
Year: 2015

Expected Result: May 5, 2015

Thanks in advance.

 

[marlan]

hi,
Try looking into Dateadd() function: http://www.techonthenet.com/access/functions/date/dateadd.php

Basically: I would take the date and add days to it:

Dateadd("d", Weeknumber*7, CDate("1/1/" & MyYear))



(CDate - Converts a string to a date)

ATB!

 

[spikepl]

Your result depends on which convention for week numbers applies to your case (country and/or company policy).

Read the link very carefully, http://www.snb-vba.eu/VBA_ISO_weeknummer_en.html

Check the section "VBA: calculate a certain day in a certain ISO-week":

 

[aubrey1983]

Hi

Thanks marlan for replying so fast, appreciate it.

Anyway I tried the first solution, meaning almost just copying and pasted in my query. here is the result.

WeekNumber,Day,DATEX
19,4,5/14/2015

It a little bit off by 8 days. I noticed, that you did'nt used the Day number? why? but if i adjust it like minus 8 in the formula, it will be ok. but that means the day will be static.

So if i make it dynamic by using the day number will it work? is my syntax below makes it right?

DATEX: DateAdd("d",([WeekNumber]*6)+[Day]+7,CDate("1/1/" & "2015"))

Result:
WeekNumber,Day,DATEX
19,4,5/6/2015

=======================================================

For 2nd reply from spike, this is really interesting, i agree it depends on our company policy, i'll have to confirm with them. Anyway, i'll definitely read on it. and apply it in my formula.

=======================================================

 

[marlan]

I noticed, that you did'nt used the Day number? why?

Yup, you are right about that, Sorry...

So if i make it dynamic by using the day number will it work? is my syntax below makes it right?

DATEX: DateAdd("d",([WeekNumber]*6)+[Day]+7,CDate("1/1/" & "2015"))

Result:
WeekNumber,Day,DATEX
19,4,5/6/2015

looking at it again, It seems inaccurate. I converted weeks to days, you are looking for the first Tuesday in the 19th week.

As for your formula, why do you multiply WeekNumber by 6? Why add 7?
and as for the day offset, we maybe did not include Jan 1st.

Again - look into the documentation

 

[namliam]

Also things may get more complex depending on where in the world you are.
We europeans dont start our weeknumbering on Jan 1st, so you cant start from Jan 1st, but instead will have to make it dynamic which would require a function instead of a "simple" inline calculation.