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)

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())

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.

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

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

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

This article has been dead for over six months. Start a new discussion instead.