Re: How do you convert week # back to date?

[alicejwz]

Re: How do you convert week # back to date?

Hi all,

How do you convert the week # back to a date?
I used datepart function to get the week #
ex:
mfg_dt_num = DatePart("ww", Forms!start_sample!earliest_mfg_dt)

and now I need to know how I can convert it back to the date format and display in a text box.

I tried format & datevalue functions they don't work.

Appreciate your assistance.

 

[MCantu]

Quesiton

I am not as experienced, butI see you did not get a response yet.
I am not familar with this function.

Does it give you what week number in the year? like 1-52?

 

[FoFa]

Use DATEADD function, use HELP is your best bet.

 

[alicejwz]

Re: How do you convert week# back to date?

Thank you for your replies.
I realized that after I posted the question but you have provided me a possible solution.

Thanks much!

 

[ColinEssex]

I think you can find out the first day of the given week number, this would be a sunday. It would of course assume that the week number in in the current calendar year.

I won't look it up unless you need it

Col

 

[alicejwz]

Re: How do you convert week # back to date?

Thanks Col for your reply.
That might work for me but I'll take a closer look and let you know.
Thanks!!
Alice

 

[rfear]

Re: How do you convert week # back to date?

Was this ever solved ?

I have year and week number that I want to convert to Monday of said week/year.

For example,

year = 2009
week = 13

date = 23/03/09.

Rich.

 

[gemma-the-husky]

Re: How do you convert week # back to date?

not sure if datepart is similar to format

using format to generate a weekn0, you can specify alternate ways of determining when week 1 starts.

so that in reverse, to evaluate the weekno for a date, you DO need to know when week 1 is, so that you can say

mydate = dateofweek1 + 7 * weekno

 

[Rabbie]

Re: How do you convert week # back to date?

not sure if datepart is similar to format

using format to generate a weekn0, you can specify alternate ways of determining when week 1 starts.

so that in reverse, to evaluate the weekno for a date, you DO need to know when week 1 is, so that you can say

mydate = dateofweek1 + 7 * weekno

Actually You need a slight amendment

mydate = dateofweek1 + 7 * (weekno -1)

 

[gemma-the-husky]

Re: How do you convert week # back to date?

thats aircode for you

you often miss something until you test it, dont you - good spot

 

[rfear]

Re: How do you convert week # back to date?

I'm confused ( it happens quite often ).

I don't see how the syntax converts a year number ( e.g. 2009 ) and a week number ( e.g. 12 ) into a date, for example the Monday of the week ( 16/03/09 ) ?

I've tried various formulae but they fall over when the year changes e.g. 2006, the arithmatic falls over.

Rich.

 

[gemma-the-husky]

Re: How do you convert week # back to date?

the point is

if you want to find week 12

then you need to know when week 1 starts

if week 1 starts on 3/1/09 (3rd Jan) then week 12 will be 11 weeks (77 days) after 3/1/09. which youcan just add (startdate + 7 * weekno - 1)

but you need to define when week 1 starts, as this is not an absolute, and depends on your wroking calendar.

 

[rfear]

Re: How do you convert week # back to date?

I see.

... so I can't just use a year and a week then ?

... will have to give this some thought, because the week number keeps resetting itself each year ( 2006, 1-52 : 2007, 1-52 : 2008, 1-52 etc ). It's not as easy as putting a stick in the ground and seeing how far from that stick I am.

 

[gemma-the-husky]

Re: How do you convert week # back to date?

the other way is to store a weekno/date table, and just look it up from that

store 52 monday's say, weekno and date

especially useful if your company year doesnt start in January

 

[raskew]

Re: How do you convert week # back to date?

Hi -

You might give this a try:

Public Function fWkNumToDate(pWkNum As Integer, pYear As Integer) As String
'Purpose:   Return the start and end dates of a week
'           based on the year and week number (based on
'           the first full week of the year, starting on
'           a Sunday).
'Coded by:  raskew
'Inputs:    1) ? fWkNumToDate(1, 2008)
'           2) ? fWkNumToDate(32, 2008)

'Outputs:   1) 1/6/2008 - 1/12/2008
'           2) 8/10/2008 - 8/16/2008

Dim dteStart As Date
Dim dteHold  As Date
Dim intHold  As Integer
 
   dteHold = DateSerial(pYear, 1, 1)
   intHold = WeekDay(dteHold)
   dteStart = IIf(intHold <> 1, dteHold - intHold + 8, dteHold)
   
   fWkNumToDate = dteStart + (7 * ((pWkNum) - 1)) & " - " & dteStart + (7 * (pWkNum) - 1)

End Function

HTH - Bob

 

[nofar]

Re: How do you convert week # back to date?

Where do you put this function and how do you link it to your database to see the results?

thank you