excel formula for the week according to the start date-CodePudding

I want to fill in the "week" column with excel formulas for the week according to the start date of the data. Please Solution.

For Week 1 starting from row 2 and counting every 7 rows.

Thanks

DATE	WEEK
01/12/2022	Week 1
02/12/2022	Week 1
03/12/2022	Week 1
04/12/2022	Week 1
06/12/2022	Week 1
07/12/2022	Week 1
08/12/2022	Week 2
09/12/2022	Week 2
10/12/2022	Week 2
11/12/2022	Week 2
12/12/2022	Week 2
13/12/2022	Week 2
14/12/2022	Week 2
15/12/2022	Week 3

CodePudding user response：

I think that Anders Balari is right. Normally the answer of =weeknum(A1) is week 49. You want to substract (49-1) 48 weeks from it so you can 'start' week 1 on december 1st. If you want that, you should make a function with a code like:

Sub week()

    Dim intWeek As Integer
    Dim intCounter, intCounterNew As Integer
    Dim intLastRow As Integer
    Dim wsData As Worksheet
    'assuming the dates are in column 1 starting in row 1
    Set wsData = ThisWorkbook.ActiveSheet
      
    intLastRow = wsData.Cells(Rows.Count, 1).End(xlUp).Row
    
    intCounterNew = 1
    
    For intCounter = 1 To intLastRow
        
        intWeek = WorksheetFunction.WeekNum(wsData.Cells(intCounter, 1))
        
        If intCounter <> 1 Then
            If intWeek = WorksheetFunction.WeekNum(wsData.Cells(intCounter - 1, 1)) Then
                wsData.Cells(intCounter, 2) = "week " & intWeek - (intWeek - intCounterNew)
            Else
                intCounterNew = intCounterNew   1
                wsData.Cells(intCounter, 2) = "week " & intWeek - (intWeek - intCounterNew)
            End If
        Else
            wsData.Cells(1, 2) = "week " & intWeek - (intWeek - intCounterNew)
        End If
        
    Next intCounter

End Sub

Be aware I made no function, but a macro that goes through the set of dates. I tried and it worked.

I don't know how to create a function like that.

CodePudding user response：

Use a formula like the one displayed. The English functions are ROUNDUP() and DAYS().

The following refers to a case in which you need calendar weeks instead:

Try to use the WEEKNUM() function, it returns the calendar week of a date. If you determine the week of the starting date, you can solve this via simple substraction for any later week. (KALENDERWOCHE() is the German version of this function)

CodePudding user response：

As I understand you want to fill that column with WeekNum() values, right? Then:

Range('B2:B16').FormulaR1C1 = '=Weeknum(Offset(R[0]C[0],0,-1))'

EDIT: I think you mean that you want to enumerate starting row 2 and add text "Week" in front:

Range('B2:B16').FormulaR1C1 = '= "Week " & TEXT(CEILING.MATH( (ROW()-1)/7),"00")'

EDIT2: Excel 2010 version compatible (that you should have said from the start):

Range('B2:B16').Formula = '= "Week " & TEXT(CEILING( (ROW()-1)/7,1),"00")'