Week number and day number...

I have to have a number that denotes the week number (the number of weeks
that have past this year) for example this week is the 44th week. then a
number that denotes the day of the week 01-07 (Monday would be 01) so today
being wed. would be 4403. Is there a simple way to do this?
thank you
Michael

Take a look at the DatePart function.
DatePart("ww",SomeDateField)* 100 + DatePart("w",SomeDateField, 2)

I suggest you check out the DatePart function in Visual Basic help as there
are additional parameters you can use to specify which day of the week is
the first day of the week and how to define the first week of the year. Be
aware that if you are using these in a query, you will need to use the
values of the vbConstants (vbMonday = 2)

"Michael" wrote in message
...
I have to have a number that denotes the week number (the number of weeks
that have past this year) for example this week is the 44th week. then a
number that denotes the day of the week 01-07 (Monday would be 01) so
today
being wed. would be 4403. Is there a simple way to do this?
thank you
Michael

thank you John
"John Spencer" ha scritto nel messaggio
...
Take a look at the DatePart function.
DatePart("ww",SomeDateField)* 100 + DatePart("w",SomeDateField, 2)

I suggest you check out the DatePart function in Visual Basic help as
there are additional parameters you can use to specify which day of the
week is the first day of the week and how to define the first week of the
year. Be aware that if you are using these in a query, you will need to
use the values of the vbConstants (vbMonday = 2)

"Michael" wrote in message
...
I have to have a number that denotes the week number (the number of weeks
that have past this year) for example this week is the 44th week. then a
number that denotes the day of the week 01-07 (Monday would be 01) so
today
being wed. would be 4403. Is there a simple way to do this?
thank you
Michael

Thank you John
I have looked at the help and I am still stuck. I do not understand what
should be in the 1st somedate field or the use of the * 100. The help
explains the 1st week of the year is the week in which Jan 1 falls is the
default week so I cannot imagine what datefield should be in the first part.

=DatePart("ww",SomeDateField)* 100 + DatePart("w",firstdayofweek, 2)

trying to use the help template ( DatePart («interval»; «date»;
«firstweekday»; «firstweek») ) for datepart I tried to get just the week by
using this =DatePart([ww];Now();[vbFirstJan1])

also this =DatePart([ww];Now();2;1)

sorry I am confused, can you help me a little other or point me to somewhere
else to find an answer?
Michael


"John Spencer" ha scritto nel messaggio
...
Take a look at the DatePart function.
DatePart("ww",SomeDateField)* 100 + DatePart("w",SomeDateField, 2)

I suggest you check out the DatePart function in Visual Basic help as
there are additional parameters you can use to specify which day of the
week is the first day of the week and how to define the first week of the
year. Be aware that if you are using these in a query, you will need to
use the values of the vbConstants (vbMonday = 2)

"Michael" wrote in message
...
I have to have a number that denotes the week number (the number of weeks
that have past this year) for example this week is the 44th week. then a
number that denotes the day of the week 01-07 (Monday would be 01) so
today
being wed. would be 4403. Is there a simple way to do this?
thank you
Michael

First, DatePart takes a string as the first parameter.

As to the * 100, you said you wanted a NUMBER not a string of numbers. To
get that I need to move the week number over two places (multiply by 100)
and then add the weekday to that. IF you just need a string of number
characters:
Format(DatePart("ww",SomeDateField,2),"00") & DatePart("w",firstdayofweek,
2)

That gets the week number and formats it as two number characters - so week
1 to 9 are shown as 01 to 09 then gets the day number and tacks the number
onto the end, so you end up with 013 for the first week, 3rd day.

DatePart("ww",Now()) will return the week number based on the which week has
january 1 in it being week 1
Your other choices for the first week are the first week in the year that
has at least 4 days in it in the year. So if Jan 1 was on Saturday, the
week with Jan 1 would NOT be the first week since (depending on your start
day) there would only be 1 or 2 days in that week.


"Michael" wrote in message
...
Thank you John
I have looked at the help and I am still stuck. I do not understand what
should be in the 1st somedate field or the use of the * 100. The help
explains the 1st week of the year is the week in which Jan 1 falls is the
default week so I cannot imagine what datefield should be in the first
part.

=DatePart("ww",SomeDateField)* 100 + DatePart("w",firstdayofweek, 2)

trying to use the help template ( DatePart («interval»; «date»;
«firstweekday»; «firstweek») ) for datepart I tried to get just the week
by using this =DatePart([ww];Now();[vbFirstJan1])

also this =DatePart([ww];Now();2;1)

sorry I am confused, can you help me a little other or point me to
somewhere else to find an answer?
Michael


"John Spencer" ha scritto nel messaggio
...
Take a look at the DatePart function.
DatePart("ww",SomeDateField)* 100 + DatePart("w",SomeDateField, 2)

I suggest you check out the DatePart function in Visual Basic help as
there are additional parameters you can use to specify which day of the
week is the first day of the week and how to define the first week of the
year. Be aware that if you are using these in a query, you will need to
use the values of the vbConstants (vbMonday = 2)

"Michael" wrote in message
...
I have to have a number that denotes the week number (the number of weeks
that have past this year) for example this week is the 44th week. then a
number that denotes the day of the week 01-07 (Monday would be 01) so
today
being wed. would be 4403. Is there a simple way to do this?
thank you
Michael

Thank you John,
If I understand what you said in this post, would the following sql give me a number as 4 digits... such as 4401?

=Format((DatePart("ww",firstweekofyear,2),"00") & DatePart("w",firstdayofweek,2)"00")

I am not sure if firstweekofyear is what should replace "SomeDateField" or not?

I am also experiencing a strange problem that may be related to me being in Europe and the comma having some other values. Every time I try to enter this sql, I get the following error.
"The expression you entered contains invalid syntax, you omitted an operand or operator, you entered an invalid
character or comma, or you entered text without surrounding it in quotation marks:"

In another posting Allen Browne suggested that I try working directly in SQL but that does not seem to work. Do you have any suggestions regarding the above code or the error I am receiving?

thank you again for all your help
Michael







"John Spencer" ha scritto nel messaggio ...
First, DatePart takes a string as the first parameter.

As to the * 100, you said you wanted a NUMBER not a string of numbers. To
get that I need to move the week number over two places (multiply by 100)
and then add the weekday to that. IF you just need a string of number
characters:
Format(DatePart("ww",SomeDateField,2),"00") & DatePart("w",firstdayofweek,
2)

That gets the week number and formats it as two number characters - so week
1 to 9 are shown as 01 to 09 then gets the day number and tacks the number
onto the end, so you end up with 013 for the first week, 3rd day.

DatePart("ww",Now()) will return the week number based on the which week has
january 1 in it being week 1
Your other choices for the first week are the first week in the year that
has at least 4 days in it in the year. So if Jan 1 was on Saturday, the
week with Jan 1 would NOT be the first week since (depending on your start
day) there would only be 1 or 2 days in that week.


"Michael" wrote in message
...
Thank you John
I have looked at the help and I am still stuck. I do not understand what
should be in the 1st somedate field or the use of the * 100. The help
explains the 1st week of the year is the week in which Jan 1 falls is the
default week so I cannot imagine what datefield should be in the first
part.

=DatePart("ww",SomeDateField)* 100 + DatePart("w",firstdayofweek, 2)

trying to use the help template ( DatePart («interval»; «date»;
«firstweekday»; «firstweek») ) for datepart I tried to get just the week
by using this =DatePart([ww];Now();[vbFirstJan1])

also this =DatePart([ww];Now();2;1)

sorry I am confused, can you help me a little other or point me to
somewhere else to find an answer?
Michael


"John Spencer" ha scritto nel messaggio
...
Take a look at the DatePart function.
DatePart("ww",SomeDateField)* 100 + DatePart("w",SomeDateField, 2)

I suggest you check out the DatePart function in Visual Basic help as
there are additional parameters you can use to specify which day of the
week is the first day of the week and how to define the first week of the
year. Be aware that if you are using these in a query, you will need to
use the values of the vbConstants (vbMonday = 2)

"Michael" wrote in message
...
I have to have a number that denotes the week number (the number of weeks
that have past this year) for example this week is the 44th week. then a
number that denotes the day of the week 01-07 (Monday would be 01) so
today
being wed. would be 4403. Is there a simple way to do this?
thank you
Michael

Well, you left out the Format function on the second part of the call.

=Format(DatePart("ww",Now(),2),"00") & Format(DatePart("w",Now(),2),"00")

That expression will return 4504

If you are in Europe, you are probably using ";" as a separater vice ",".
So replace the commas (,) with semi-colons (.

What are FirstWeekOfYear and FirstDayofWeek? For the above to work you
should be using a date field or variable and it should be the SAME
field/variable in both parts. For example, I used the Now() function which
returns the current system date and time.

"Michael" wrote in message
...
Thank you John,
If I understand what you said in this post, would the following sql give me
a number as 4 digits... such as 4401?

=Format((DatePart("ww",firstweekofyear,2),"00") &
DatePart("w",firstdayofweek,2)"00")

I am not sure if firstweekofyear is what should replace "SomeDateField" or
not?

I am also experiencing a strange problem that may be related to me being in
Europe and the comma having some other values. Every time I try to enter
this sql, I get the following error.
"The expression you entered contains invalid syntax, you omitted an
operand or operator, you entered an invalid
character or comma, or you entered text without surrounding it in quotation
marks:"

In another posting Allen Browne suggested that I try working directly in SQL
but that does not seem to work. Do you have any suggestions regarding the
above code or the error I am receiving?

thank you again for all your help
Michael







"John Spencer" ha scritto nel messaggio
...
First, DatePart takes a string as the first parameter.

As to the * 100, you said you wanted a NUMBER not a string of numbers. To
get that I need to move the week number over two places (multiply by 100)
and then add the weekday to that. IF you just need a string of number
characters:
Format(DatePart("ww",SomeDateField,2),"00") & DatePart("w",firstdayofweek,
2)

That gets the week number and formats it as two number characters - so
week
1 to 9 are shown as 01 to 09 then gets the day number and tacks the number
onto the end, so you end up with 013 for the first week, 3rd day.

DatePart("ww",Now()) will return the week number based on the which week
has
january 1 in it being week 1
Your other choices for the first week are the first week in the year that
has at least 4 days in it in the year. So if Jan 1 was on Saturday, the
week with Jan 1 would NOT be the first week since (depending on your start
day) there would only be 1 or 2 days in that week.


"Michael" wrote in message
...
Thank you John
I have looked at the help and I am still stuck. I do not understand what
should be in the 1st somedate field or the use of the * 100. The help
explains the 1st week of the year is the week in which Jan 1 falls is the
default week so I cannot imagine what datefield should be in the first
part.

=DatePart("ww",SomeDateField)* 100 + DatePart("w",firstdayofweek, 2)

trying to use the help template ( DatePart («interval»; «date»;
«firstweekday»; «firstweek») ) for datepart I tried to get just the week
by using this =DatePart([ww];Now();[vbFirstJan1])

also this =DatePart([ww];Now();2;1)

sorry I am confused, can you help me a little other or point me to
somewhere else to find an answer?
Michael


"John Spencer" ha scritto nel messaggio
...
Take a look at the DatePart function.
DatePart("ww",SomeDateField)* 100 + DatePart("w",SomeDateField, 2)

I suggest you check out the DatePart function in Visual Basic help as
there are additional parameters you can use to specify which day of the
week is the first day of the week and how to define the first week of
the
year. Be aware that if you are using these in a query, you will need to
use the values of the vbConstants (vbMonday = 2)

"Michael" wrote in message
...
I have to have a number that denotes the week number (the number of
weeks
that have past this year) for example this week is the 44th week. then
a
number that denotes the day of the week 01-07 (Monday would be 01) so
today
being wed. would be 4403. Is there a simple way to do this?
thank you
Michael

Thank you for both answers, I have no idea why I was putting those names
there except that I saw them on the help page and thought that is what
should be there... also the ";" solved the other problem. I have never had
that problem before and have been working here in Europe for 5 years.

thank you
Michael

"John Spencer" ha scritto nel messaggio
...
Well, you left out the Format function on the second part of the call.

=Format(DatePart("ww",Now(),2),"00") & Format(DatePart("w",Now(),2),"00")

That expression will return 4504

If you are in Europe, you are probably using ";" as a separater vice ",".
So replace the commas (,) with semi-colons (.

What are FirstWeekOfYear and FirstDayofWeek? For the above to work you
should be using a date field or variable and it should be the SAME
field/variable in both parts. For example, I used the Now() function
which returns the current system date and time.

"Michael" wrote in message
...
Thank you John,
If I understand what you said in this post, would the following sql give
me a number as 4 digits... such as 4401?

=Format((DatePart("ww",firstweekofyear,2),"00") &
DatePart("w",firstdayofweek,2)"00")

I am not sure if firstweekofyear is what should replace "SomeDateField" or
not?

I am also experiencing a strange problem that may be related to me being
in Europe and the comma having some other values. Every time I try to
enter this sql, I get the following error.
"The expression you entered contains invalid syntax, you omitted an
operand or operator, you entered an invalid
character or comma, or you entered text without surrounding it in
quotation marks:"

In another posting Allen Browne suggested that I try working directly in
SQL but that does not seem to work. Do you have any suggestions regarding
the above code or the error I am receiving?

thank you again for all your help
Michael







"John Spencer" ha scritto nel messaggio
...
First, DatePart takes a string as the first parameter.

As to the * 100, you said you wanted a NUMBER not a string of numbers.
To
get that I need to move the week number over two places (multiply by 100)
and then add the weekday to that. IF you just need a string of number
characters:
Format(DatePart("ww",SomeDateField,2),"00") &
DatePart("w",firstdayofweek,
2)

That gets the week number and formats it as two number characters - so
week
1 to 9 are shown as 01 to 09 then gets the day number and tacks the
number
onto the end, so you end up with 013 for the first week, 3rd day.

DatePart("ww",Now()) will return the week number based on the which week
has
january 1 in it being week 1
Your other choices for the first week are the first week in the year that
has at least 4 days in it in the year. So if Jan 1 was on Saturday, the
week with Jan 1 would NOT be the first week since (depending on your
start
day) there would only be 1 or 2 days in that week.


"Michael" wrote in message
...
Thank you John
I have looked at the help and I am still stuck. I do not understand what
should be in the 1st somedate field or the use of the * 100. The help
explains the 1st week of the year is the week in which Jan 1 falls is
the
default week so I cannot imagine what datefield should be in the first
part.

=DatePart("ww",SomeDateField)* 100 + DatePart("w",firstdayofweek, 2)

trying to use the help template ( DatePart («interval»; «date»;
«firstweekday»; «firstweek») ) for datepart I tried to get just the week
by using this =DatePart([ww];Now();[vbFirstJan1])

also this =DatePart([ww];Now();2;1)

sorry I am confused, can you help me a little other or point me to
somewhere else to find an answer?
Michael


"John Spencer" ha scritto nel messaggio
...
Take a look at the DatePart function.
DatePart("ww",SomeDateField)* 100 + DatePart("w",SomeDateField, 2)

I suggest you check out the DatePart function in Visual Basic help as
there are additional parameters you can use to specify which day of the
week is the first day of the week and how to define the first week of
the
year. Be aware that if you are using these in a query, you will need
to
use the values of the vbConstants (vbMonday = 2)

"Michael" wrote in message
...
I have to have a number that denotes the week number (the number of
weeks
that have past this year) for example this week is the 44th week. then
a
number that denotes the day of the week 01-07 (Monday would be 01) so
today
being wed. would be 4403. Is there a simple way to do this?
thank you
Michael

Back to give you more abuse.....


the following should give me this week as number 44 as it used the first
week where Jan 1 enters the year.

=Format(DatePart("ww";Now();1);"00") & Format(DatePart("w";Now();2);"00")

I cannot use the 1st week where there are 4 days as all the calendars here
show this week as the 44th week and both the new code above and the old code
below show the week as the 45th week.

=Format(DatePart("ww",Now(),2),"00") & Format(DatePart("w",Now(),2),"00")

michael






"Michael" ha scritto nel messaggio
...
Thank you for both answers, I have no idea why I was putting those names
there except that I saw them on the help page and thought that is what
should be there... also the ";" solved the other problem. I have never had
that problem before and have been working here in Europe for 5 years.

thank you
Michael

"John Spencer" ha scritto nel messaggio
...
Well, you left out the Format function on the second part of the call.

=Format(DatePart("ww",Now(),2),"00") & Format(DatePart("w",Now(),2),"00")

That expression will return 4504

If you are in Europe, you are probably using ";" as a separater vice ",".
So replace the commas (,) with semi-colons (.

What are FirstWeekOfYear and FirstDayofWeek? For the above to work you
should be using a date field or variable and it should be the SAME
field/variable in both parts. For example, I used the Now() function
which returns the current system date and time.

"Michael" wrote in message
...
Thank you John,
If I understand what you said in this post, would the following sql give
me a number as 4 digits... such as 4401?

=Format((DatePart("ww",firstweekofyear,2),"00") &
DatePart("w",firstdayofweek,2)"00")

I am not sure if firstweekofyear is what should replace "SomeDateField"
or not?

I am also experiencing a strange problem that may be related to me being
in Europe and the comma having some other values. Every time I try to
enter this sql, I get the following error.
"The expression you entered contains invalid syntax, you omitted an
operand or operator, you entered an invalid
character or comma, or you entered text without surrounding it in
quotation marks:"

In another posting Allen Browne suggested that I try working directly in
SQL but that does not seem to work. Do you have any suggestions regarding
the above code or the error I am receiving?

thank you again for all your help
Michael







"John Spencer" ha scritto nel messaggio
...
First, DatePart takes a string as the first parameter.

As to the * 100, you said you wanted a NUMBER not a string of numbers.
To
get that I need to move the week number over two places (multiply by
100)
and then add the weekday to that. IF you just need a string of number
characters:
Format(DatePart("ww",SomeDateField,2),"00") &
DatePart("w",firstdayofweek,
2)

That gets the week number and formats it as two number characters - so
week
1 to 9 are shown as 01 to 09 then gets the day number and tacks the
number
onto the end, so you end up with 013 for the first week, 3rd day.

DatePart("ww",Now()) will return the week number based on the which week
has
january 1 in it being week 1
Your other choices for the first week are the first week in the year
that
has at least 4 days in it in the year. So if Jan 1 was on Saturday, the
week with Jan 1 would NOT be the first week since (depending on your
start
day) there would only be 1 or 2 days in that week.


"Michael" wrote in message
...
Thank you John
I have looked at the help and I am still stuck. I do not understand
what
should be in the 1st somedate field or the use of the * 100. The help
explains the 1st week of the year is the week in which Jan 1 falls is
the
default week so I cannot imagine what datefield should be in the first
part.

=DatePart("ww",SomeDateField)* 100 + DatePart("w",firstdayofweek, 2)

trying to use the help template ( DatePart («interval»; «date»;
«firstweekday»; «firstweek») ) for datepart I tried to get just the
week
by using this =DatePart([ww];Now();[vbFirstJan1])

also this =DatePart([ww];Now();2;1)

sorry I am confused, can you help me a little other or point me to
somewhere else to find an answer?
Michael


"John Spencer" ha scritto nel messaggio
...
Take a look at the DatePart function.
DatePart("ww",SomeDateField)* 100 + DatePart("w",SomeDateField, 2)

I suggest you check out the DatePart function in Visual Basic help as
there are additional parameters you can use to specify which day of
the
week is the first day of the week and how to define the first week of
the
year. Be aware that if you are using these in a query, you will need
to
use the values of the vbConstants (vbMonday = 2)

"Michael" wrote in message
...
I have to have a number that denotes the week number (the number of
weeks
that have past this year) for example this week is the 44th week.
then a
number that denotes the day of the week 01-07 (Monday would be 01) so
today
being wed. would be 4403. Is there a simple way to do this?
thank you
Michael

lol.. prob talking to myself by now as I you prob saw my thank you and are
not monitoring this thread anymore, but the problem is opposite to what I
thought. The code is using the first week that Jan 1 appears and not the
second as it should by using the "2". I am getting 45 as the week and not
44.

thanks michael



"Michael" ha scritto nel messaggio
...
Back to give you more abuse.....


the following should give me this week as number 44 as it used the first
week where Jan 1 enters the year.

=Format(DatePart("ww";Now();1);"00") & Format(DatePart("w";Now();2);"00")

I cannot use the 1st week where there are 4 days as all the calendars here
show this week as the 44th week and both the new code above and the old
code below show the week as the 45th week.

=Format(DatePart("ww",Now(),2),"00") & Format(DatePart("w",Now(),2),"00")

michael






"Michael" ha scritto nel messaggio
...
Thank you for both answers, I have no idea why I was putting those names
there except that I saw them on the help page and thought that is what
should be there... also the ";" solved the other problem. I have never
had that problem before and have been working here in Europe for 5 years.

thank you
Michael

"John Spencer" ha scritto nel messaggio
...
Well, you left out the Format function on the second part of the call.

=Format(DatePart("ww",Now(),2),"00") &
Format(DatePart("w",Now(),2),"00")

That expression will return 4504

If you are in Europe, you are probably using ";" as a separater vice
",". So replace the commas (,) with semi-colons (.

What are FirstWeekOfYear and FirstDayofWeek? For the above to work you
should be using a date field or variable and it should be the SAME
field/variable in both parts. For example, I used the Now() function
which returns the current system date and time.

"Michael" wrote in message
...
Thank you John,
If I understand what you said in this post, would the following sql give
me a number as 4 digits... such as 4401?

=Format((DatePart("ww",firstweekofyear,2),"00") &
DatePart("w",firstdayofweek,2)"00")

I am not sure if firstweekofyear is what should replace "SomeDateField"
or not?

I am also experiencing a strange problem that may be related to me being
in Europe and the comma having some other values. Every time I try to
enter this sql, I get the following error.
"The expression you entered contains invalid syntax, you omitted an
operand or operator, you entered an invalid
character or comma, or you entered text without surrounding it in
quotation marks:"

In another posting Allen Browne suggested that I try working directly in
SQL but that does not seem to work. Do you have any suggestions
regarding the above code or the error I am receiving?

thank you again for all your help
Michael







"John Spencer" ha scritto nel messaggio
...
First, DatePart takes a string as the first parameter.

As to the * 100, you said you wanted a NUMBER not a string of numbers.
To
get that I need to move the week number over two places (multiply by
100)
and then add the weekday to that. IF you just need a string of number
characters:
Format(DatePart("ww",SomeDateField,2),"00") &
DatePart("w",firstdayofweek,
2)

That gets the week number and formats it as two number characters - so
week
1 to 9 are shown as 01 to 09 then gets the day number and tacks the
number
onto the end, so you end up with 013 for the first week, 3rd day.

DatePart("ww",Now()) will return the week number based on the which
week has
january 1 in it being week 1
Your other choices for the first week are the first week in the year
that
has at least 4 days in it in the year. So if Jan 1 was on Saturday,
the
week with Jan 1 would NOT be the first week since (depending on your
start
day) there would only be 1 or 2 days in that week.


"Michael" wrote in message
...
Thank you John
I have looked at the help and I am still stuck. I do not understand
what
should be in the 1st somedate field or the use of the * 100. The help
explains the 1st week of the year is the week in which Jan 1 falls is
the
default week so I cannot imagine what datefield should be in the first
part.

=DatePart("ww",SomeDateField)* 100 + DatePart("w",firstdayofweek, 2)

trying to use the help template ( DatePart («interval»; «date»;
«firstweekday»; «firstweek») ) for datepart I tried to get just the
week
by using this =DatePart([ww];Now();[vbFirstJan1])

also this =DatePart([ww];Now();2;1)

sorry I am confused, can you help me a little other or point me to
somewhere else to find an answer?
Michael


"John Spencer" ha scritto nel messaggio
...
Take a look at the DatePart function.
DatePart("ww",SomeDateField)* 100 + DatePart("w",SomeDateField, 2)

I suggest you check out the DatePart function in Visual Basic help as
there are additional parameters you can use to specify which day of
the
week is the first day of the week and how to define the first week of
the
year. Be aware that if you are using these in a query, you will need
to
use the values of the vbConstants (vbMonday = 2)

"Michael" wrote in message
...
I have to have a number that denotes the week number (the number of
weeks
that have past this year) for example this week is the 44th week.
then a
number that denotes the day of the week 01-07 (Monday would be 01)
so
today
being wed. would be 4403. Is there a simple way to do this?
thank you
Michael