Not sure this solves the problems for you, but
Format(DatePart("ww",Now(),2,3),"00") &
Format(DatePart("w",Now(),2),"00")
returns 4404
The 3 means the the first full week of the year is week 1. Again
remember to replace the commas with semi-colons.
For 2004 the first full week starts on Jan 5 using the above (Monday is
the first day of the week and the week must be a complete week.
For 2005 the first full week starts on Jan 3
For 2003 the first full week start on Jan 6
If you can't work it out using the formula then you may have to resort
to building a table that tell you which date is in which year and week.
You will have to figure out the rules.
Good Luck
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" <
[email protected]> 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" <
[email protected]> 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" <
[email protected]> 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.
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" <
[email protected]> 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.
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" <
[email protected]> 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)
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
