Another Dateformat problem "From text format 2005124 to yyyywwd"

[frontside]

I´m importing some data to access. One field is a datefield which is formatted as text like this: 2005064. I need this datefield to be formatted as yyyywwd (year, weeknr, daynr of the week)
2005064
2005 = year
06 = weeknr
4 = Day of week "thursday"

I need the date in this format so I could subtract 4 weeks from the date.

Would love som help
Getting a little bit tired of all the dateformat problems out there.

Thanks
Micke "Sweden

 

[Frink]

Hallo,

If you want to convert you Datefield format to an Access date:
You can use the string slicing routines Left$, Mid$, and Right$ to extract the year, week and day. Then use DateSerial to get the date value for the 1st jan that year, then add 7 days per week and the day number, subtract 1 as we already started on 1st Jan and Bob's your uncle!

strDateField="2005064"
datDate=DateSerial(Val(Left$(strDate, 4)), 1, 1) + 7 * Val(Mid$(strDate, 5, 2)) + Val(Mid$(strDate, 7, 1)) - 1
debug.print datDate

- Frink