0

I need help in making code to extract the year and week number from the date. I needed to segregate orders by week, not on individual days. I need to get format yy, WW. In excel function I can write something like this:

=CONCATENATE(RIGHT(YEAR(P13);2);",";TEXT(WEEKNUM(P13);"00"))

but I can't write it in VBA code.

Yaser Darzi
  • 1,480
  • 12
  • 24
marwineper
  • 21
  • 1
  • 1
    Can you at least include what you tried in VBA, along with some sample data and expected output – JvdV Oct 20 '19 at 11:21

2 Answers2

1
D = now()
For i = 0 To t - 1

ActiveCell.Offset(0, i) = Application.WorksheetFunction.Right(Year(D + c * 7), 2)) & "," & Application.WorksheetFunction.WeekNum(D + c * 7)

c = c + 1

Next i

data - (after formating)

03.02.2020 - (20,06)

27.12.2019 - (19,52)

27.12.2019 - (19,52)

Techie
  • 181
  • 4
  • 14
marwineper
  • 21
  • 1
0

Using native VBA functions, something like:

Function vbYrWN(dt As Date) As String
    vbYrWN = Format(dt, "yy") & _
        Application.International(xlDecimalSeparator) & _
            Format(Format(dt, "ww"), "00")

End Function

If you want to hard-code the comma separator, just replace Application.International(xlDecimalSeparator) with ","

Note that the defaults for the first day of week and first week of year are the same for the VBA Format function as they are for the Excel WEEKNUM function

EDIT

Based on the comments, it seems the OP does NOT want to use the Excel default definition of WEEKNUMBER.

One can use the ISOweeknumber and probably avoid the issue of missing a serial YR,WN. However, one would have to add a test to adjust the year for those instances when a December date is really in Week 1 of the subsequent year.

I suggest trying:

Edit to work around bug in VBA Date functions

also year will correspond with weeknumber at the beginning/end of the year

Option Explicit
Function vbYrWN(dt As Date) As String
    Dim yr As Date
    If DatePart("ww", dt - Weekday(dt, vbMonday) + 4, vbMonday, vbFirstFourDays) = 1 And _
        DatePart("y", dt) > 350 Then
        yr = DateSerial(Year(dt) + 1, 1, 1)
    ElseIf DatePart("ww", dt - Weekday(dt, vbMonday) + 4, vbMonday, vbFirstFourDays) >= 52 And _
        DatePart("y", dt) <= 7 Then
        yr = DateSerial(Year(dt), 1, 0)
    Else
        yr = dt
    End If

    vbYrWN = Format(yr, "yy") & _
        Application.International(xlDecimalSeparator) & _
            Format(Format(dt - Weekday(dt, vbMonday) + 4, "ww", vbMonday, vbFirstFourDays), "00")
End Function

Additional Comments

  • You can replace DatePart("ww", dt - Weekday(dt, vbMonday) + 4, vbMonday, vbFirstFourDays) with Application.WorksheetFunction.IsoWeekNum(dt). I'm not sure which method is more efficient, although I generally prefer using native VBA functions in place of Worksheet functions when available.

  • Modifying your looping code a bit, it seems to work OK here, filling rows 1 and 2 with yy,ww and the corresponding date in row 2 (I added row 2 fort for error checking). Doesn't miss any weeks.

Sub test()
 Dim c As Long, i As Long, t As Long
 Dim R As Range
 Dim D As Date

 D = #12/25/2019#
 Set R = Range("A1")
    R.EntireRow.NumberFormat = "@"
 t = 10

 c = 0
 For i = 0 To t - 1
    R.Offset(0, i) = vbYrWN(D + c * 7)
    R.Offset(1, i) = D + c * 7
    c = c + 1
Next i

End Sub

enter image description here

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • thanks it looks great but is one problem in end of year in wey I use this code it skip 20,01. ActiveCell.Offset(0, i) = Format(D + c * 7, "yy") & "," & Format(Format(D + c * 7, "ww"), "00") It's probably because 19,53 and 20,01 is this same weak. – marwineper Oct 20 '19 at 13:46
  • @marwineper How are you using it? What is the input? What is the result? – Ron Rosenfeld Oct 20 '19 at 13:48
  • input is curent date =now() And im using simple loop c = 0 For i = 0 To t - 1 ActiveCell.Offset(0, i) = Format(D + c * 7, "yy") & "," & Format(Format(D + c * 7, "ww"), "00") c = c + 1 Next i – marwineper Oct 20 '19 at 13:52
  • @marwineper What is the **date** that you think should be week 01 that you think is incorrect? – Ron Rosenfeld Oct 20 '19 at 13:56
  • this loop is to be sorted byweeks but the sorted items have specific dates so they don't skip any week – marwineper Oct 20 '19 at 13:56
  • in efect of this loop i get (19,52 19,53 20,02 20,03) so I miss 20,01. week 20,01 is between 01.01.2020-05.01.2020. – marwineper Oct 20 '19 at 13:58
  • @marwineper What is the **date** that you think should be week 01 that you think is incorrect? – Ron Rosenfeld Oct 20 '19 at 13:59
  • @marwineper. 05.01.2020 is in week 2 according to the definition of week you are using. – Ron Rosenfeld Oct 20 '19 at 14:15
  • for me important is that i could get weeks numbers which includes 20,01 – marwineper Oct 20 '19 at 14:29
  • @marwineper Then you will need to use a different definition of weeknumber. The VBA function I provided seems to give the same answers as your Excel function, which you indicated was correct. Was that not the case? – Ron Rosenfeld Oct 20 '19 at 14:33
  • @marwineper See my Edit for a suggestion – Ron Rosenfeld Oct 20 '19 at 14:45
  • Generally, your way is good, I just need a way to go through each week what I have now skips three days between 1.01.2020-3.01.2020 which are important to me . – marwineper Oct 20 '19 at 14:50
  • @marwineper Having noticed the code returned an incorrect week for `30-Dec-2019` I just edited the code again. It seems that some of the VBA date functions have an error where they do not recognize that century years divided by 400 are leap years. The edited code is a work around for the errors that causes and seems to work for `30-Dec-2019` – Ron Rosenfeld Oct 20 '19 at 20:36
  • @marwineper I may have made an incorrect assumption in my code. In the case when a date in a given year is in a week of the next or previous year, what year should be used for `yy`? For example, `30-Dec-2019` is week `1` using ISO weeknumbers. And also, `1-Jan-2016` is week `53`. What should `yy` be in each of those instances. – Ron Rosenfeld Oct 20 '19 at 23:58