Convert Week/Yr to Month/1/Yr

[peryan77]

I need help converting Week/Yr to Month/1/Yr. I have the week/yr in a table.

Thanks.

 

[Blorf]

The week / year data is week number, ie 1, 2, 3.... for say 2003?

Also, is the week stored as 01,02,03 or just 1/,2/,3/?

Try something like this:

=format (datevalue ("01/01/" & right([yourfield], 4) + (left ([yourfield], 2) * 7), "MM/01/YYYY")

This assumes a format like this: 01/2003.

ChaZ


Ascii dumb question, get a dumb Ansi

 

[peryan77]

The week and the year values are stored in seperate tables.

 

[peryan77]

I tried it your way my combinging values into one field, and I used your expression and it gives me expression contains wrong # of arguments.

 

[Blorf]

Post your formula as you typed it. Probably I put a ) in the wrong place, but I don't see it.

ChaZ

Ascii dumb question, get a dumb Ansi

 

[peryan77]

I just copied and pasted your function, and the ) seems to be ok.

Anyway,

I came up with this b/c I decided I wanted the first day of the week. I know I could use first day of week function instead of -7 but it gives same result. My one weakness is manipulating dates. Never had to do it much.


DateAdd("d",(([DIAD_Battery_Info]![Week]*7)-7),FormatDateTime("1/1/ " & [DIAD_Battery_Info]![Yr]))

 

[Blorf]

Cool.

Ascii dumb question, get a dumb Ansi

 

[raskew]

How will you handle the scenario where the specified week starts in one month and ends in the next? For example:

Week 18 of 2004 starts on 25-Apr-04 and ends 1-May-04.

Just wondering,

Bob

 

[peryan77]

Access seems to be pulling the month when the first day of the week falls. Ok by me.