By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,705 Members | 1,852 Online
Bytes IT Community
+ Ask a Question
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
  1. dteDate = Me.txtCollDate
  2. WeekNumber = Format(dteDate, "ww")
  3. Me.txtWeek = WeekNumber
  4.  
  5. If WeekNumber = 1 Then
  6. FirstDayOfWeek = "1/1/" & Year(dteDate)
  7. LastDayOfWeek = dteDate + (7 - Weekday(dteDate))
  8. Period = Format(FirstDayOfWeek, "mm/dd/yy") & "-" & Format(LastDayOfWeek, "mm/dd/yy")
  9. End If
  10.  
  11. If WeekNumber > 1 And WeekNumber < 53 Then
  12. FirstDayOfWeek = dteDate - (Weekday(dteDate) - 1)
  13. LastDayOfWeek = dteDate - Weekday(dteDate) + 7
  14. Period = Format(FirstDayOfWeek, "mm/dd/yy") & "-" & Format(LastDayOfWeek, "mm/dd/yy")
  15. End If
  16.  
  17. If WeekNumber = 53 Then
  18. WeekNumber = 52
  19. FirstDayOfWeek = (dteDate - (Weekday(dteDate) - 1)) - 7
  20. LastDayOfWeek = "12/31/" & Year(dteDate)
  21. Period = Format(FirstDayOfWeek, "mm/dd/yy") & "-" & Format(LastDayOfWeek, "mm/dd/yy")
  22. End If
  23.  
Jan 11 '08 #1
Share this Question
Share on Google+
4 Replies


Rabbit
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
  1. dteDate = Me.txtCollDate
  2. WeekNumber = Format(dteDate, "ww")
  3. Me.txtWeek = WeekNumber
  4.  
  5. If WeekNumber = 1 Then
  6. FirstDayOfWeek = "1/1/" & Year(dteDate)
  7. LastDayOfWeek = dteDate + (7 - Weekday(dteDate))
  8. Period = Format(FirstDayOfWeek, "mm/dd/yy") & "-" & Format(LastDayOfWeek, "mm/dd/yy")
  9. End If
  10.  
  11. If WeekNumber > 1 And WeekNumber < 53 Then
  12. FirstDayOfWeek = dteDate - (Weekday(dteDate) - 1)
  13. LastDayOfWeek = dteDate - Weekday(dteDate) + 7
  14. Period = Format(FirstDayOfWeek, "mm/dd/yy") & "-" & Format(LastDayOfWeek, "mm/dd/yy")
  15. End If
  16.  
  17. If WeekNumber = 53 Then
  18. WeekNumber = 52
  19. FirstDayOfWeek = (dteDate - (Weekday(dteDate) - 1)) - 7
  20. LastDayOfWeek = "12/31/" & Year(dteDate)
  21. Period = Format(FirstDayOfWeek, "mm/dd/yy") & "-" & Format(LastDayOfWeek, "mm/dd/yy")
  22. End If
  23.  
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

Rabbit
Expert Mod 10K+
P: 12,355
This should do it, I didn't test it yet.
Expand|Select|Wrap|Line Numbers
  1. 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

Rabbit
Expert Mod 10K+
P: 12,355
After some testing, it looks like you should use:

Expand|Select|Wrap|Line Numbers
  1. 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")))
  2.  
Jan 15 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.