Home / Questions / 329999
Accepted
JCstdio
Asked: 2020-02-18 07:12:42 +0800 CST 2020-02-18 07:12:42 +0800 CST

VBA (or excel)Filter by week number in a table

  • 772

I am trying to find a way to put an autofilter on an excel table. By default I can put the current, past and next week filters.

But what I want to do now is that I can take the week number of a cell and filter the date field of the table by that week number.

Thanks.

excel

2 Answers

  1. You can create a helper column and use the functions:

    =NUM.DE.SEMANA(A3)

    O well

    =ISO.NUM.DE.SEMANA(A3)

    And apply filters using that new column. Obviously cell A3 contains a date.

    • 1
  2. Best Answer

    I finally found this, which works for me:

    • With the number of the year and the week, I calculate the first and last day of the week. Surely it will be a little unbalanced at the end and beginning of the year.

      nYear = shdict.Cells(2, "C")
nWeek = shdict.Cells(4, "C")

inputDate = DateSerial(nYear, 1, 1)
inputDate = DateAdd("ww", nWeek - 1, inputDate)
wd = Weekday(inputDate, vbMonday)

StartDate = DateAdd("d", 1 - wd, inputDate)
LastDate = StartDate + 7
    • 0