Member Avatar for shena

Hi friends,

How do i calculate the week number of a year? The week number should be calculated from the first day of a year. For example, this year 1st January 2010 falled on Friday, so week 1 should be calculcated from Friday(01/01/2010) till Thursday (07/01/2010). therefore, given any date, the current week must be able to be calculated automatically. Please help me with this problem.
I used below code, but it's limited by putting vbFriday.

Dim currentWeek As String
currentWeek = DatePart("ww", Now, vbFriday)
  • 2 Contributors
  • 5 Replies
  • 5K Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by shena

Dani AI

Generated

If you want week 1 to always begin on Jan 1 and each subsequent week to be a 7-day block from there (e.g., in 2010: Fri 01/01 to Thu 01/07), do not use the built-in calendar week rules. Instead, either anchor to Jan 1 and count whole 7-day chunks, or tell DatePart to start the week on whatever day-of-week Jan 1 falls on for that year.

Example 1: count 7-day blocks from Jan 1 (works for any year and any date).

Dim d As Date          ' The date you want to evaluate
d = Now                ' Or assign any other date
Dim start As Date
start = DateSerial(Year(d), 1, 1)

Dim weekNum As Long
weekNum = (DateDiff("d", start, d) \ 7) + 1

Example 2: use DatePart but compute the first day-of-week dynamically from Jan 1, and force the first week to start on Jan 1.

Dim d As Date
d = Now                ' Or any date
Dim start As Date
start = DateSerial(Year(d), 1, 1)

Dim weekNum As Long
weekNum = DatePart("ww", d, Weekday(start), vbFirstJan1)

Both approaches avoid hardcoding vbFriday and will work across years. The first always yields 52 or 53 weeks depending on the year length; the last week may be shorter. See Microsoft docs for DatePart, DateDiff, and Weekday for parameter details and edge cases.

Recommended Answers

All 5 Replies

Member Avatar for AndreRet

You can use the DateDiff function as in -

Dim iNumberOfTheWeek As Integer
iNumberOfTheWeek = DateDiff("ww", "Jan-01-2010", Now())

MsgBox iNumberOfTheWeek

Or the datepart function to return the current week number as in -

Dim iNumberOfTheWeek As Integer
iNumberOfTheWeek = DatePart("ww", Now())
Member Avatar for shena

Hi,

Thanks for your reply.
I used this code:

Dim iNumberOfTheWeek As Integer
iNumberOfTheWeek = DateDiff("ww", "Jan-01-2010", Now()) 
MsgBox iNumberOfTheWeek

As for today is 3rd September 2010, I suppose to get week 36 (Friday is the first day of the week), instead i get 35 using your code. It was week 35 till yesterday and it should be week 36 from today (Friday) till next Thursday. Please help me.

Member Avatar for AndreRet

Its counting the days from the 1st of Jan 2010, hence add more days -

Dim iNumberOfTheWeek As Integer
iNumberOfTheWeek = DateDiff("ww", "Jan-01-2010", (Now() + 2))
MsgBox iNumberOfTheWeek
Member Avatar for shena

Hi AndreRet,

Does that imply that this coding can be used for upcoming years without having to change anything especially this line

iNumberOfTheWeek = DateDiff("ww", "Jan-01-2010", (Now() + 2))

??

Thanks/Shena

Member Avatar for shena

Hi, sorry i didnt notice that the year is set to be 2010. How to represent the year with a variable so that it can automatically count for the week nummber every year?
Thanks/Shena

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.