0

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
16
Views
6 Years
Discussion Span
Last Post by shena
0

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

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.

0

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
0

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

0

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 topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.