asked on

Return week as week 1 when December 31 is selecrtet.

Me.txtWeekNum.Text = DatePart(DateInterval.WeekOfYear, CDate(txtWeekEnding.Text), Microsoft.VisualBasic.FirstDayOfWeek.Monday)
Which works fine for all weeks except if December 31 is selected in the Calendar which case the week returned is 53. I would like the week to be 1 for the first week in January. How can this be done?

ASKER CERTIFIED SOLUTION

bigdaddyz99

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bigdaddyz99

You can also add another parameter to DatePart which specifies what constitutes the first week of the year. It defaults to Jan. 1, which is the way you have it, making Dec. 31 fall into week 53.

However, you can specify other settings also.
FirstWeekOfYear.System=First week of year specified in system settings
FirstWeekOfYear.Jan1=Week in which January 1 occurs (default)
FirstWeekOfYear.FirstFourDays=First week that has at least four days in the new year (complies with ISO standard 8601, section 3.17)

For 2007, just because of the day of the week Jan 1 fell on, it happened that only Dec. 31 was in week 53. But, for 2008, there are more days in week 53 due to the first week being short because Jan 1 fell in the middle of the week.

If you let it default to week of Jan1 as the first week of the year, you should be able to just let week 53 roll over into the next year's week 1.

Hope this makes sense.

FirstWeekOfYear.FirstFullWeek
3
First full week in new year

ee_rlee

try this

Me.txtWeekNum.Text = DatePart(DateInterval.WeekOfYear, Dateadd(DateInterval.Day, CDate(txtWeekEnding.Text), 7 - datepart(DateInterval.Weekday, Date(txtWeekEnding.Text), Microsoft.VisualBasic.FirstDayOfWeek.Monday)), Microsoft.VisualBasic.FirstDayOfWeek.Monday)