VBA 将日期转换为周数

VBA实现日期转换为周数

vba
Sub ConvertDateToWeek()
    Dim myDate As Date
    Dim weekNumber As Integer
    
    myDate = #10/1/2021# '设置一个日期
    
    weekNumber = DatePart("ww", myDate) '获取该日期所在的周数
    
    MsgBox "日期 " & myDate & " 所在的周数是 " & weekNumber
End Sub

vba
Sub CalculateWeeklySales()
    Dim salesData As Range
    Dim weeklySales As Worksheet
    Dim weeklyTotal As Range
    Dim currentRow As Long
    Dim currentWeek As Integer
    
    '设置销售数据的范围
    Set salesData = Sheets("销售数据").Range("A2:B100")
    
    '创建一个新的工作表用于存储每周的销售额
    Set weeklySales = Sheets.Add
    weeklySales.Name = "每周销售额"
    
    '在新的工作表中设置表头
    weeklySales.Range("A1") = "周数"
    weeklySales.Range("B1") = "销售额"
    
    '开始遍历销售数据
    For currentRow = 1 To salesData.Rows.Count
        '获取当前行的日期和销售额
        Dim currentDate As Date
        Dim currentSales As Double
        currentDate = salesData.Cells(currentRow, 1).Value
        currentSales = salesData.Cells(currentRow, 2).Value
        
        '将日期转换为周数
        currentWeek = DatePart("ww", currentDate)
        
        '在新的工作表中查找当前周数的销售额
        Set weeklyTotal = weeklySales.Columns("A").Find(currentWeek, LookIn:=xlValues, LookAt:=xlWhole)
        
        '如果找到了当前周数的销售额，则累加销售额
        If Not weeklyTotal Is Nothing Then
            weeklyTotal.Offset(0, 1).Value = weeklyTotal.Offset(0, 1).Value + currentSales
        Else
            '如果没有找到当前周数的销售额，则添加一行并设置销售额
            weeklySales.Cells(weeklySales.Cells(Rows.Count, "A").End(xlUp).Row + 1, "A").Value = currentWeek
            weeklySales.Cells(weeklySales.Cells(Rows.Count, "A").End(xlUp).Row, "B").Value = currentSales
        End If
    Next currentRow
    
    MsgBox "每周的销售额已经统计完成。"
End Sub