Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

VBA Convert date to week number

Tags:

date

excel

vba

In VBA I want to convert a date as 03/11/2017(DD/MM/YYYY) into the week number for that date.

Until now I have the following code:

   'geting the date out of the string
    HeadlineTemp = Mid(VRHeadline, InStr(VRHeadline, "[") + 1, 10)
   'switch "." to "/"
    HeadlineTemp = Replace(HeadlineTemp, ".", "/")
   'convert to a date
    FristVRFirstKW = CDate(HeadlineTemp)

Now, I need a function to convert that date into the week number of the year. First week day is Monday.

like image 696
USER7423 Avatar asked Oct 27 '17 11:10

USER7423


People also ask

What is the difference between Isoweeknum and Weeknum?

WeekNum uses the week containing January 1 as the first week of the year. The result from this function can range from 1 to 54. ISOWeekNum uses the week containing the first Thursday of the year as the first week of the year. This follows the ISO 8601 date and time standard definition for week numbering.

How does the Weeknum formula work in Excel?

The WEEKNUM function takes a date and returns a number between 1 and 54 that corresponds to the week of the year. By default, the WEEKNUM function starts counting on the week that contains January 1 and increments week numbers on Sunday. The WEEKNUM function accepts two arguments, serial_num and return_type.

What is Isoweeknum?

The ISOWEEKNUM Function[1] in Excel is a Date & Time Function used for finding out the ISO week number of the year for the given date value (e.g., week number 37 of 52 weeks in a year). The function was introduced in MS Excel 2013.


2 Answers

To make the Week Number with Monday as a first day, use the following:

WorksheetFunction.WeekNum(now, vbMonday)
like image 118
Vityata Avatar answered Oct 06 '22 20:10

Vityata


Using VBA, to convert a date into an isoWeeknumber, you merely need the DatePart function (where DT is the date of interest):

isoWeekNumber = DatePart("ww", DT, vbMonday, vbFirstFourDays)

If you want to use other definitions than that specified in ISO 8601, investigate some of the other options for FirstDayOfWeek and FirstWeekOfYear

NOTE

As pointed out by @Mike85, there is a bug in DatePart (and also in the Format) function wherein Monday may be erroneously given a weeknumber of 53 when it should be 1.

There are a variety of workarounds.

In Excel 2013+ (Excel for Mac 2011+) you can use for the ISO Weeknumber:

isoWeekNumber = WorksheetFunction.isoWeekNum(dt)

For earlier versions, you can test the Monday and adjust it if necessary, or you can write a separate routine.

like image 36
Ron Rosenfeld Avatar answered Oct 06 '22 20:10

Ron Rosenfeld