convert text to date

[Guest]

Hello,

Could anyone help me - Im trying to covert text to date.

The text represents a date in the format of year, week and day of week e.g.
2007421 (yyyywwd ?). Sometimes the day is not indicated.

I would like to convert this text to a date. If the day is not indicated, i
would like to take the last day of the working week i.e. friday.

i.e. i would like to modify, i think, the following formula:

CDate(Format([text];"\0000\-00\-00"))

Once stored as a date i would like to use Datediff, this i know how to do.

Im not any good with visual basic so if i could run a query it would be nice.

i much appreciate any replies.

i hope i have provided enough info.

sten

 

[Guest]

The big question: January 1, 2008 is on a Tuesday. In this case when does the
first week start? What week is January 8, 2008 in? January 5, 2008? January
6, 2008? December 31st, 2008?

This needs to be hammered out first as the first and last days of the year
can fall on different days of the week.

 

[Pieter Wijnen]

You can this to get you started (Returns the First Day of the Week)

Public Function Week2Date(WeekNo As Long, Optional ByVal Yr As Long =0 ) As
Date
Dim Jan1 As Date
Dim Sub1 As Boolean
Dim Ret As Date

if Yr = 0 Then
Jan1 = VBA.DateSerial(VBA.Year(VBA.Date()), 1, 1)
Else
Jan1 = VBA.DateSerial(yr, 1, 1)
End If
'Sub1 = (VBA.Format(Jan1, "ww", VBA.vbMonday, VBA.vbFirstFourDays) = 1)
Sub1 = (VBA.Format(Jan1, "ww", VBA.VbDayOfWeek.vbUseSystemDayOfWeek,
VBA.VbFirstWeekOfYear.vbUseSystem) = 1)
Ret = VBA.DateAdd("ww", WeekNo + Sub1, Jan1)
Ret = Ret - VBA.Weekday(Ret, VBA.VbDayOfWeek.vbSunday) + 7
Week2Date = Ret
End Function

HtH
Pieter

The big question: January 1, 2008 is on a Tuesday. In this case when does
the
first week start? What week is January 8, 2008 in? January 5, 2008?
January
6, 2008? December 31st, 2008?

This needs to be hammered out first as the first and last days of the year
can fall on different days of the week.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello,

Could anyone help me - Im trying to covert text to date.

The text represents a date in the format of year, week and day of week
e.g.
2007421 (yyyywwd ?). Sometimes the day is not indicated.

I would like to convert this text to a date. If the day is not indicated,
i
would like to take the last day of the working week i.e. friday.

i.e. i would like to modify, i think, the following formula:

CDate(Format([text];"\0000\-00\-00"))

Once stored as a date i would like to use Datediff, this i know how to
do.

Im not any good with visual basic so if i could run a query it would be
nice.

i much appreciate any replies.

i hope i have provided enough info.

sten

 

[John W. Vinson]

Hello,

Could anyone help me - Im trying to covert text to date.

The text represents a date in the format of year, week and day of week e.g.
2007421 (yyyywwd ?). Sometimes the day is not indicated.

Another ambiguity in addition to Jerry's: is 200744 Wednesday the fourth day
of the fourth week (counting from Sunday), or is it Friday of the 44th week?
How on Earth can you tell?

Whoever designed this date format should be compelled to manually correct all
the entries... :-{(


John W. Vinson [MVP]

 

[Guest]

Thanks for the reply.

Good question, i would like to think that the first day of the week is
monday and the first week is the week where 1st of January falls i.e.
December 31st 2008 is the first day of the first week of 2009.

But looking back some years accordning to this logic look really strange.

Could you suggest a logic ? is there a defined logic for Sweden ?

The big question: January 1, 2008 is on a Tuesday. In this case when does the
first week start? What week is January 8, 2008 in? January 5, 2008? January
6, 2008? December 31st, 2008?

This needs to be hammered out first as the first and last days of the year
can fall on different days of the week.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello,

Could anyone help me - Im trying to covert text to date.

The text represents a date in the format of year, week and day of week e.g.
2007421 (yyyywwd ?). Sometimes the day is not indicated.

I would like to convert this text to a date. If the day is not indicated, i
would like to take the last day of the working week i.e. friday.

i.e. i would like to modify, i think, the following formula:

CDate(Format([text];"\0000\-00\-00"))

Once stored as a date i would like to use Datediff, this i know how to do.

Im not any good with visual basic so if i could run a query it would be nice.

i much appreciate any replies.

i hope i have provided enough info.

sten

 

[Guest]

As i said, im not any good with VBA. Ive used a module once to change
pictures in a report accordning to a field but apart from that im a true
novice.

So how do i use this code. can i use it in a query ?

For your informatoin, Im pulling information from the companys databas i.e.
the input is in our ERP and im trying to produce a report with the data using
the datadiff function.

You can this to get you started (Returns the First Day of the Week)

Public Function Week2Date(WeekNo As Long, Optional ByVal Yr As Long =0 ) As
Date
Dim Jan1 As Date
Dim Sub1 As Boolean
Dim Ret As Date

if Yr = 0 Then
Jan1 = VBA.DateSerial(VBA.Year(VBA.Date()), 1, 1)
Else
Jan1 = VBA.DateSerial(yr, 1, 1)
End If
'Sub1 = (VBA.Format(Jan1, "ww", VBA.vbMonday, VBA.vbFirstFourDays) = 1)
Sub1 = (VBA.Format(Jan1, "ww", VBA.VbDayOfWeek.vbUseSystemDayOfWeek,
VBA.VbFirstWeekOfYear.vbUseSystem) = 1)
Ret = VBA.DateAdd("ww", WeekNo + Sub1, Jan1)
Ret = Ret - VBA.Weekday(Ret, VBA.VbDayOfWeek.vbSunday) + 7
Week2Date = Ret
End Function

HtH
Pieter

The big question: January 1, 2008 is on a Tuesday. In this case when does
the
first week start? What week is January 8, 2008 in? January 5, 2008?
January
6, 2008? December 31st, 2008?

This needs to be hammered out first as the first and last days of the year
can fall on different days of the week.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello,

Could anyone help me - Im trying to covert text to date.

The text represents a date in the format of year, week and day of week
e.g.
2007421 (yyyywwd ?). Sometimes the day is not indicated.

I would like to convert this text to a date. If the day is not indicated,
i
would like to take the last day of the working week i.e. friday.

i.e. i would like to modify, i think, the following formula:

CDate(Format([text];"\0000\-00\-00"))

Once stored as a date i would like to use Datediff, this i know how to
do.

Im not any good with visual basic so if i could run a query it would be
nice.

i much appreciate any replies.

i hope i have provided enough info.

sten

 

[Guest]

Another ambiguity in addition to Jerry's: is 200744 Wednesday the fourth day

of the fourth week (counting from Sunday), or is it Friday of the 44th week?
How on Earth can you tell?

Good question.

Well the companys ERP would produce 2007044 for the fourth day of the fourth
week i.e. Thursday (counting from Monday - which i want to count from) not
200744.

 

[Pieter Wijnen]

I think Sweden has the same as Norway, ie that the first Week of the year is
the first week with at least four days in it
You can copy my code to any general module & use it in a query
It is based on the regional settings for first week & first day of week, but
the remmed out line (')
contains the norwegian (swedish) settings

Pieter

Thanks for the reply.

Good question, i would like to think that the first day of the week is
monday and the first week is the week where 1st of January falls i.e.
December 31st 2008 is the first day of the first week of 2009.

But looking back some years accordning to this logic look really strange.

Could you suggest a logic ? is there a defined logic for Sweden ?

The big question: January 1, 2008 is on a Tuesday. In this case when does
the
first week start? What week is January 8, 2008 in? January 5, 2008?
January
6, 2008? December 31st, 2008?

This needs to be hammered out first as the first and last days of the
year
can fall on different days of the week.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello,

Could anyone help me - Im trying to covert text to date.

The text represents a date in the format of year, week and day of week
e.g.
2007421 (yyyywwd ?). Sometimes the day is not indicated.

I would like to convert this text to a date. If the day is not
indicated, i
would like to take the last day of the working week i.e. friday.

i.e. i would like to modify, i think, the following formula:

CDate(Format([text];"\0000\-00\-00"))

Once stored as a date i would like to use Datediff, this i know how to
do.

Im not any good with visual basic so if i could run a query it would be
nice.

i much appreciate any replies.

i hope i have provided enough info.

sten

 

[Guest]

Thanks a lot Pieter, this code will help me tremendously. I think i see the
solution infront of me now its only to implement.

Week2Date («WeekNo»; «Yr»)

Could you please tweak the code
so that if
e.g. i insert week 43 and 2007 then i want to get 20071022 not 2007-10-27
e.g. i insert week 44 and 2007 then i want to get 20071029 not 2007-11-03

much appreciate your help.

I think Sweden has the same as Norway, ie that the first Week of the year is
the first week with at least four days in it
You can copy my code to any general module & use it in a query
It is based on the regional settings for first week & first day of week, but
the remmed out line (')
contains the norwegian (swedish) settings

Pieter

Thanks for the reply.

Good question, i would like to think that the first day of the week is
monday and the first week is the week where 1st of January falls i.e.
December 31st 2008 is the first day of the first week of 2009.

But looking back some years accordning to this logic look really strange.

Could you suggest a logic ? is there a defined logic for Sweden ?

The big question: January 1, 2008 is on a Tuesday. In this case when does
the
first week start? What week is January 8, 2008 in? January 5, 2008?
January
6, 2008? December 31st, 2008?

This needs to be hammered out first as the first and last days of the
year
can fall on different days of the week.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hello,

Could anyone help me - Im trying to covert text to date.

The text represents a date in the format of year, week and day of week
e.g.
2007421 (yyyywwd ?). Sometimes the day is not indicated.

I would like to convert this text to a date. If the day is not
indicated, i
would like to take the last day of the working week i.e. friday.

i.e. i would like to modify, i think, the following formula:

CDate(Format([text];"\0000\-00\-00"))

Once stored as a date i would like to use Datediff, this i know how to
do.

Im not any good with visual basic so if i could run a query it would be
nice.

i much appreciate any replies.

i hope i have provided enough info.

sten

 

[Pieter Wijnen]

it returns a date, so just add format around it
ie
Format(Week2Date(26,2007),"yyyymmdd")

HtH

Pieter

Thanks a lot Pieter, this code will help me tremendously. I think i see
the
solution infront of me now its only to implement.

Week2Date («WeekNo»; «Yr»)

Could you please tweak the code
so that if
e.g. i insert week 43 and 2007 then i want to get 20071022 not 2007-10-27
e.g. i insert week 44 and 2007 then i want to get 20071029 not 2007-11-03

much appreciate your help.

I think Sweden has the same as Norway, ie that the first Week of the year
is
the first week with at least four days in it
You can copy my code to any general module & use it in a query
It is based on the regional settings for first week & first day of week,
but
the remmed out line (')
contains the norwegian (swedish) settings

Pieter

Thanks for the reply.

Good question, i would like to think that the first day of the week is
monday and the first week is the week where 1st of January falls i.e.
December 31st 2008 is the first day of the first week of 2009.

But looking back some years accordning to this logic look really
strange.

Could you suggest a logic ? is there a defined logic for Sweden ?

:

The big question: January 1, 2008 is on a Tuesday. In this case when
does
the
first week start? What week is January 8, 2008 in? January 5, 2008?
January
6, 2008? December 31st, 2008?

This needs to be hammered out first as the first and last days of the
year
can fall on different days of the week.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hello,

Could anyone help me - Im trying to covert text to date.

The text represents a date in the format of year, week and day of
week
e.g.
2007421 (yyyywwd ?). Sometimes the day is not indicated.

I would like to convert this text to a date. If the day is not
indicated, i
would like to take the last day of the working week i.e. friday.

i.e. i would like to modify, i think, the following formula:

CDate(Format([text];"\0000\-00\-00"))

Once stored as a date i would like to use Datediff, this i know how
to
do.

Im not any good with visual basic so if i could run a query it would
be
nice.

i much appreciate any replies.

i hope i have provided enough info.

sten

 

[Guest]

Thanks Pieter.

your code, accordning to my logic, gives me the wrong week and it gives the
wrong day.

I would like to get monday, not saturday, as the first day.

Your code

Could you please tweak the code
so that if
e.g. i insert week 43 and 2007 then i would like to get 20071022 not 2007-10-27
e.g. i insert week 44 and 2007 then i would like to get 20071029 not 2007-11-03

much appreciate your help.

thanks Pieter.

Sten
----------------------------------------------------

it returns a date, so just add format around it
ie
Format(Week2Date(26,2007),"yyyymmdd")

HtH

Pieter

Thanks a lot Pieter, this code will help me tremendously. I think i see
the
solution infront of me now its only to implement.

Week2Date («WeekNo»; «Yr»)

Could you please tweak the code
so that if
e.g. i insert week 43 and 2007 then i want to get 20071022 not 2007-10-27
e.g. i insert week 44 and 2007 then i want to get 20071029 not 2007-11-03

much appreciate your help.

I think Sweden has the same as Norway, ie that the first Week of the year
is
the first week with at least four days in it
You can copy my code to any general module & use it in a query
It is based on the regional settings for first week & first day of week,
but
the remmed out line (')
contains the norwegian (swedish) settings

Pieter

Thanks for the reply.

Good question, i would like to think that the first day of the week is
monday and the first week is the week where 1st of January falls i.e.
December 31st 2008 is the first day of the first week of 2009.

But looking back some years accordning to this logic look really
strange.

Could you suggest a logic ? is there a defined logic for Sweden ?

:

The big question: January 1, 2008 is on a Tuesday. In this case when
does
the
first week start? What week is January 8, 2008 in? January 5, 2008?
January
6, 2008? December 31st, 2008?

This needs to be hammered out first as the first and last days of the
year
can fall on different days of the week.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hello,

Could anyone help me - Im trying to covert text to date.

The text represents a date in the format of year, week and day of
week
e.g.
2007421 (yyyywwd ?). Sometimes the day is not indicated.

I would like to convert this text to a date. If the day is not
indicated, i
would like to take the last day of the working week i.e. friday.

i.e. i would like to modify, i think, the following formula:

CDate(Format([text];"\0000\-00\-00"))

Once stored as a date i would like to use Datediff, this i know how
to
do.

Im not any good with visual basic so if i could run a query it would
be
nice.

i much appreciate any replies.

i hope i have provided enough info.

sten

 

[Pieter Wijnen]

Mea Culpa
(Testing sometimes helps)

Public Function Week2Date(WeekNo As Long, Optional ByVal Yr As Long = 0, _
Optional ByVal FDOW As VBA.VbDayOfWeek =
VBA.VbDayOfWeek.vbUseSystemDayOfWeek, _
Optional ByVal FWOY As VBA.VbFirstWeekOfYear =
VBA.VbFirstWeekOfYear.vbUseSystem) As Date
' Returns First Day of week
Dim Jan1 As Date
Dim Sub1 As Boolean
Dim Ret As Date

If Yr = 0 Then
Jan1 = VBA.DateSerial(VBA.Year(VBA.Date()), 1, 1)
Else
Jan1 = VBA.DateSerial(Yr, 1, 1)
End If
Sub1 = (VBA.Format(Jan1, "ww", FDOW, FWOY) = 1)
Ret = VBA.DateAdd("ww", WeekNo + Sub1, Jan1)
Ret = Ret - VBA.Weekday(Ret, FDOW) + 1
Week2Date = Ret
End Function

Added Two Optional Parameters to override system settings for FirstDayOfWeek
& FirstWeekOfYear

Hth

Pieter

Thanks Pieter.

your code, accordning to my logic, gives me the wrong week and it gives
the
wrong day.

I would like to get monday, not saturday, as the first day.

Your code

Could you please tweak the code
so that if
e.g. i insert week 43 and 2007 then i would like to get 20071022 not
2007-10-27
e.g. i insert week 44 and 2007 then i would like to get 20071029 not
2007-11-03

much appreciate your help.

thanks Pieter.

Sten
----------------------------------------------------

it returns a date, so just add format around it
ie
Format(Week2Date(26,2007),"yyyymmdd")

HtH

Pieter

Thanks a lot Pieter, this code will help me tremendously. I think i see
the
solution infront of me now its only to implement.

Week2Date («WeekNo»; «Yr»)

Could you please tweak the code
so that if
e.g. i insert week 43 and 2007 then i want to get 20071022 not
2007-10-27
e.g. i insert week 44 and 2007 then i want to get 20071029 not
2007-11-03

much appreciate your help.




:

I think Sweden has the same as Norway, ie that the first Week of the
year
is
the first week with at least four days in it
You can copy my code to any general module & use it in a query
It is based on the regional settings for first week & first day of
week,
but
the remmed out line (')
contains the norwegian (swedish) settings

Pieter

Thanks for the reply.

Good question, i would like to think that the first day of the week
is
monday and the first week is the week where 1st of January falls
i.e.
December 31st 2008 is the first day of the first week of 2009.

But looking back some years accordning to this logic look really
strange.

Could you suggest a logic ? is there a defined logic for Sweden ?

:

The big question: January 1, 2008 is on a Tuesday. In this case
when
does
the
first week start? What week is January 8, 2008 in? January 5, 2008?
January
6, 2008? December 31st, 2008?

This needs to be hammered out first as the first and last days of
the
year
can fall on different days of the week.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hello,

Could anyone help me - Im trying to covert text to date.

The text represents a date in the format of year, week and day of
week
e.g.
2007421 (yyyywwd ?). Sometimes the day is not indicated.

I would like to convert this text to a date. If the day is not
indicated, i
would like to take the last day of the working week i.e. friday.

i.e. i would like to modify, i think, the following formula:

CDate(Format([text];"\0000\-00\-00"))

Once stored as a date i would like to use Datediff, this i know
how
to
do.

Im not any good with visual basic so if i could run a query it
would
be
nice.

i much appreciate any replies.

i hope i have provided enough info.

sten

 

[Guest]

thanks a bunch, it worked just the way i want it.

Mea Culpa
(Testing sometimes helps)

Public Function Week2Date(WeekNo As Long, Optional ByVal Yr As Long = 0, _
Optional ByVal FDOW As VBA.VbDayOfWeek =
VBA.VbDayOfWeek.vbUseSystemDayOfWeek, _
Optional ByVal FWOY As VBA.VbFirstWeekOfYear =
VBA.VbFirstWeekOfYear.vbUseSystem) As Date
' Returns First Day of week
Dim Jan1 As Date
Dim Sub1 As Boolean
Dim Ret As Date

If Yr = 0 Then
Jan1 = VBA.DateSerial(VBA.Year(VBA.Date()), 1, 1)
Else
Jan1 = VBA.DateSerial(Yr, 1, 1)
End If
Sub1 = (VBA.Format(Jan1, "ww", FDOW, FWOY) = 1)
Ret = VBA.DateAdd("ww", WeekNo + Sub1, Jan1)
Ret = Ret - VBA.Weekday(Ret, FDOW) + 1
Week2Date = Ret
End Function

Added Two Optional Parameters to override system settings for FirstDayOfWeek
& FirstWeekOfYear

Hth

Pieter

Thanks Pieter.

your code, accordning to my logic, gives me the wrong week and it gives
the
wrong day.

I would like to get monday, not saturday, as the first day.

Your code

Could you please tweak the code
so that if
e.g. i insert week 43 and 2007 then i would like to get 20071022 not
2007-10-27
e.g. i insert week 44 and 2007 then i would like to get 20071029 not
2007-11-03

much appreciate your help.

thanks Pieter.

Sten
----------------------------------------------------

it returns a date, so just add format around it
ie
Format(Week2Date(26,2007),"yyyymmdd")

HtH

Pieter

Thanks a lot Pieter, this code will help me tremendously. I think i see
the
solution infront of me now its only to implement.

Week2Date («WeekNo»; «Yr»)

Could you please tweak the code
so that if
e.g. i insert week 43 and 2007 then i want to get 20071022 not
2007-10-27
e.g. i insert week 44 and 2007 then i want to get 20071029 not
2007-11-03

much appreciate your help.




:

I think Sweden has the same as Norway, ie that the first Week of the
year
is
the first week with at least four days in it
You can copy my code to any general module & use it in a query
It is based on the regional settings for first week & first day of
week,
but
the remmed out line (')
contains the norwegian (swedish) settings

Pieter

Thanks for the reply.

Good question, i would like to think that the first day of the week
is
monday and the first week is the week where 1st of January falls
i.e.
December 31st 2008 is the first day of the first week of 2009.

But looking back some years accordning to this logic look really
strange.

Could you suggest a logic ? is there a defined logic for Sweden ?

:

The big question: January 1, 2008 is on a Tuesday. In this case
when
does
the
first week start? What week is January 8, 2008 in? January 5, 2008?
January
6, 2008? December 31st, 2008?

This needs to be hammered out first as the first and last days of
the
year
can fall on different days of the week.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hello,

Could anyone help me - Im trying to covert text to date.

The text represents a date in the format of year, week and day of
week
e.g.
2007421 (yyyywwd ?). Sometimes the day is not indicated.

I would like to convert this text to a date. If the day is not
indicated, i
would like to take the last day of the working week i.e. friday.

i.e. i would like to modify, i think, the following formula:

CDate(Format([text];"\0000\-00\-00"))

Once stored as a date i would like to use Datediff, this i know
how
to
do.

Im not any good with visual basic so if i could run a query it
would
be
nice.

i much appreciate any replies.

i hope i have provided enough info.

sten