Week of Year

[Brad_(Guest)]

Ok, I thought I had everything working with DTEPART(ww,date). The calculation returns a number, I thought I was set. Yet, now I am experiencing a different issue. January 7, 2007 is getting flagged as week 1. Jan. 7, 2007 should clearly be week 2. Otherwise, things work great.____Any suggestions? I may be doing something wrong.____Somehow the application must think it is a leap year or something. It continues throughout all 2007 dates. 2006 dates work perectly. Is there a setting in the rsw.ini file that needs to be added or adjusted?

[Alan_Klein]

My guess is that it "worked" in 2006 because the 1st was a Sunday. Therefore, the seventh was the end of the first week.____In 2007, the seventh is the end of the first seven days of the year, so it is the end of the first week. Not by the "Sunday through Saturday" calendar week, but by the "seven days equals a week" week. In most years (i.e., those that do not begin on a Sunday) the first week is shorter than seven days.____I believe you need to add either a Select Case function or nested IIFs to the beginning of your function to see what day of the week January 1st is on. This will allow you to figure out how long the first "week" of the year is. This information, in turn, will allow you to look at the day of the week of the current day to see if it falls in the week that the built-in function thinks it is in or if it is in the next week.____So, since 2007 started on a Monday, the first "week" actually ended on the 6th. The table of weeks would look something like this:____ Function Actual__Date Says "Week"__-----------------------------__1 1 1__2 1 1__3 1 1__4 1 1__5 1 1__6 1 1__7 1 2__8 2 2__9 2 2__10 2 2__11 2 2__12 2 2__13 2 2__14 2 3__15 3 3__etc.

[Alan_Klein]

My table looks atrocious because extra spaces got trimmed. I^ll try again below:____----- Function----- Actual__Date--- Says ----- "Week"__-----------------------------__1 -------- 1 -------- 1__2 -------- 1 -------- 1__3 -------- 1 -------- 1__4 -------- 1 -------- 1__5 -------- 1 -------- 1__6 -------- 1 -------- 1__7 -------- 1 -------- 2__8 -------- 2 -------- 2__9 -------- 2 -------- 2__10 ------- 2 -------- 2__11 ------- 2 -------- 2__12 ------- 2 -------- 2__13 ------- 2 -------- 2__14 ------- 2 -------- 3__15 ------- 3 -------- 3__etc.

[Brad_(Guest)]

Thanks for the info.____I am going to set up a lookup table as____date week__20061231 1__20070101 1__.__.__.__.__20070107 2__etc____I will then create a calculation that will convert extract the yyyymmdd from the original field and use it join to the lookup table.____Thanks again, brad