Return Conventional date from Year/Week combo

[Ken]

my data is in yyyymm (no seperator) , for example 200901 is the first week
in 2009. So i need to convert to a conventional date format, mm/dd/yy.

any ideas on how to do that via a query script or something?

 

[fredg]

my data is in yyyymm (no seperator) , for example 200901 is the first week
in 2009. So i need to convert to a conventional date format, mm/dd/yy.

any ideas on how to do that via a query script or something?

A valid date must have a Month, Day, and Year value.
Assuming you meant to write
"for example 200901 is the first **MONTH** in 2009"

you could set the date to the first day of the month.

NewDateateSerial(Left([FieldName],4), Right([FieldName],2), 1)

Or the Last day of the month.
NewDateateSerial(Left([FieldName],4), Right([FieldName],2) + 1, 0)

 

[Marshall Barton]

my data is in yyyymm (no seperator) , for example 200901 is the first week
in 2009. So i need to convert to a conventional date format, mm/dd/yy.

If you really meant to say "week", then you will have to
provide more information.

What day is the first day of a week?

How do you define the first week in a year? (The answer
here requires some serious thought.)

With this additional information, you need to calculate the
first day of the first week in any year. Then all other
weeks start date is the first week's start fate plus the
week number times seven.

 

[Ken Wenze]

First question would be what do you plan on doing in handling the day? Your
original data only has year and month!

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200907/1

no - the orginal data is year and week number - 200901 is the first
week in 2009 so 200901 = 01-05-2009

Thanks,

Ken

 

[Bob Quintal]

:

ms.aspx/access-formscoding/200907/1

no - the orginal data is year and week number - 200901 is the
first week in 2009 so 200901 = 01-05-2009

Thanks,

Ken

Linq's question is still valid. You do not have a day value in the
data.
You say it's the 5th?
I could state that the the day should be the 4th, or the 10th.
4th is the sunday that starts the week,
10th is the Saturday that ends the first workweek of the year.
Your 5th is only one of about 13 possible right answers.

Tell us your business rules and we can try to help you write an
expression or function to satisfy those rules.

 

[Ken Wenze]

:






Linq's question is still valid. You do not have a day value in the
data.
You say it's the 5th?
I could state that the the day should be the 4th, or the 10th.
4th is the sunday that starts the week,
10th is the Saturday that ends the first workweek of the year.
Your 5th is only one of about 13 possible right answers.

Tell us your business rules and we can try to help you write an
expression or function to satisfy those rules.

--
Bob Quintal

PA is y I've altered my email address.- Hide quoted text -

- Show quoted text -

Monday is the beginning of the workweek and the first full week of jan
09 (5th) Business cycles beg on mon - end on friday (no weekends)

it very well could be the ending number but i would like to know the
logic to write either code..

"Feed a man for the day - you resolve his hunger for the day; Teach a
man to Fish and you feed him for life!"

 

[Bob Quintal]

m:

Monday is the beginning of the workweek and the first full week of
jan 09 (5th) Business cycles beg on mon - end on friday (no
weekends)

it very well could be the ending number but i would like to know
the logic to write either code..

"Feed a man for the day - you resolve his hunger for the day;
Teach a man to Fish and you feed him for life!"

Public Function MyCurrentWeekMonday(Year_Week as string) as date
Dim MyYear as integer
Dim MyWeek as integer
Dim MyDay1 as integer
Dim MyFirstMonday as Date

'separate the week from the year
MyYear = val(left(Year_Week,4))
MyWeek = val(mid(Year_Week,5))

'Now find the weekday of january 1st of your year.
MyDay1 = weekday(dateserial(myYear,1,1), vbMonday)
' Find monday of week 1
MyFirstMonday = dateadd("d",8-MyDay1,dateserial(MyYear,1,1))
'add the number of weeks
MyCurrentWeekMonday = dateadd("ww",MyWeek,MyFirstMonday)
Exit function


You should add some error checking to trap garbage Year_Week inputs.