425,705 Members | 1,852 Online
Need help? Post your question and get tips & solutions from a community of 425,705 IT Pros & Developers. It's quick & easy.

# calculate weeknumber and period

 P: 2 Hello All, I would like to calculate weeknumber and period from first day of the week (sunday) and last day of the week (saturday) and week number falls from 1 to 52 only. If week number >= 53 then week number will equal to 52. And Last Day of First week is the first Saturday of January, as long as it falls at least four days into the month Example: if enter Date WeekNo. Period 12/31/06 1 12/31/06-01/06/07 01/05/07 1 12/31/06-01/06/07 01/09/07 2 01/07/07-01/13/07 12/24/07 52 12/23/07-12/29/07 12/30/07 1 12/30/07-01/05/08 12/29/08 52 12/28/08-01/03/09 01/05/09 1 01/04/09-01/10/09 Any help would be appreciated. Thanks alots. Codes below is what had for first day of week: 01/01/yyyy last day of week: 12/31/yyyy. But it's not the way i want. i would like to calculate week number base on the definition above. Thanks Expand|Select|Wrap|Line Numbers dteDate = Me.txtCollDate WeekNumber = Format(dteDate, "ww") Me.txtWeek = WeekNumber   If WeekNumber = 1 Then FirstDayOfWeek = "1/1/" & Year(dteDate) LastDayOfWeek = dteDate + (7 - Weekday(dteDate)) Period = Format(FirstDayOfWeek, "mm/dd/yy") & "-" & Format(LastDayOfWeek, "mm/dd/yy") End If   If WeekNumber > 1 And WeekNumber < 53 Then FirstDayOfWeek = dteDate - (Weekday(dteDate) - 1) LastDayOfWeek = dteDate - Weekday(dteDate) + 7 Period = Format(FirstDayOfWeek, "mm/dd/yy") & "-" & Format(LastDayOfWeek, "mm/dd/yy") End If   If WeekNumber = 53 Then WeekNumber = 52 FirstDayOfWeek = (dteDate - (Weekday(dteDate) - 1)) - 7 LastDayOfWeek = "12/31/" & Year(dteDate) Period = Format(FirstDayOfWeek, "mm/dd/yy") & "-" & Format(LastDayOfWeek, "mm/dd/yy") End If   Jan 11 '08 #1
4 Replies

 Expert Mod 10K+ P: 12,355 Hello All, I would like to calculate weeknumber and period from first day of the week (sunday) and last day of the week (saturday) and week number falls from 1 to 52 only. If week number >= 53 then week number will equal to 52. And Last Day of First week is the first Saturday of January, as long as it falls at least four days into the month Example: if enter Date WeekNo. Period 12/31/06 1 12/31/06-01/06/07 01/05/07 1 12/31/06-01/06/07 01/09/07 2 01/07/07-01/13/07 12/24/07 52 12/23/07-12/29/07 12/30/07 1 12/30/07-01/05/08 12/29/08 52 12/28/08-01/03/09 01/05/09 1 01/04/09-01/10/09 Any help would be appreciated. Thanks alots. Codes below is what had for first day of week: 01/01/yyyy last day of week: 12/31/yyyy. But it's not the way i want. i would like to calculate week number base on the definition above. Thanks Expand|Select|Wrap|Line Numbers dteDate = Me.txtCollDate WeekNumber = Format(dteDate, "ww") Me.txtWeek = WeekNumber   If WeekNumber = 1 Then FirstDayOfWeek = "1/1/" & Year(dteDate) LastDayOfWeek = dteDate + (7 - Weekday(dteDate)) Period = Format(FirstDayOfWeek, "mm/dd/yy") & "-" & Format(LastDayOfWeek, "mm/dd/yy") End If   If WeekNumber > 1 And WeekNumber < 53 Then FirstDayOfWeek = dteDate - (Weekday(dteDate) - 1) LastDayOfWeek = dteDate - Weekday(dteDate) + 7 Period = Format(FirstDayOfWeek, "mm/dd/yy") & "-" & Format(LastDayOfWeek, "mm/dd/yy") End If   If WeekNumber = 53 Then WeekNumber = 52 FirstDayOfWeek = (dteDate - (Weekday(dteDate) - 1)) - 7 LastDayOfWeek = "12/31/" & Year(dteDate) Period = Format(FirstDayOfWeek, "mm/dd/yy") & "-" & Format(LastDayOfWeek, "mm/dd/yy") End If   Please use Code tags. What you want is impossible. You have a range of dates, specifically the ones at the end of the year that lap over to the beginning of the next year, that can have more than one value but you give no way to decide between the two values. The following two records are from your example. They should either be both week number 1 or both be week number 52. They are the same type of dates but your result is inconsistent. It's arbitrary how you decide which number to assign it. 12/30/07 1 12/30/07-01/05/08 12/29/08 52 12/28/08-01/03/09 Jan 11 '08 #2

 P: 2 Hi, thanks for your reply Rabbit. So you mean there's no way I can calculate the weeknumber like this. Sample: 12/30/07 1 12/30/07-01/05/08 , it calculated in this range is week number 1 because end of week is Saturday (01/05/08) and this is the first Saturday in January and it falls at least four days into the month such as (01/01/08, 01/02/08, 01/03/08, 01/04/08 and 01/05/08). 12/29/08 52 12/28/08-01/03/09, about this one it should calculate week number 52 even though (01/03/09) is still the first Saturday in January, however, it has only 3 days into that month (such as 01/01/09, 01/02/09, 01/03/09) , can't calculate week number 1 and should be calculated week number 52. I tried too many ways but I couldn't get the result right. Does anyone else know please help, I'm very appreciated. Jan 15 '08 #3

 Expert Mod 10K+ P: 12,355 This should do it, I didn't test it yet. Expand|Select|Wrap|Line Numbers iif(((Year(DateVariable) mod 4 = 0 AND Format(DateVariable, "y") >= 364) OR (Year(DateVariable) mod 4 <> 0 AND Format(DateVariable, "y") >= 363)) AND Weekday(DateSerial(Year(DateVariable), 12, 31)) >= 4, 52, 1) Jan 15 '08 #4

 Expert Mod 10K+ P: 12,355 After some testing, it looks like you should use: Expand|Select|Wrap|Line Numbers IIf(Format(DateVariable, "ww") = Format(DateSerial(Year(DateVariable), 12, 31), "ww") Or Format(DateVariable, "ww") = 1, IIf(Weekday(DateSerial(Year(DateVariable), 12, 31)) >= 4, 52, 1), IIf(Weekday(DateSerial(Year(DateVariable), 1, 1)) >= 5, Format(DateVariable, "ww") - 1, Format(DateVariable, "ww")))   Jan 15 '08 #5