excel日期转换为周数_VBA将日期转换为周数

6 个答案:

答案 0 :(得分：12)

要将周数作为第一天的星期一，请使用以下内容：

WorksheetFunction.WeekNum(now, vbMonday)

答案 1 :(得分：6)

使用VBA将日期转换为isoWeeknumber，您只需要DatePart函数(其中DT是感兴趣的日期)：

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

如果您想使用ISO 8601中指定的其他定义，请调查FirstDayOfWeek和FirstWeekOfYear的其他一些选项

答案 2 :(得分：3)

在周数方面要小心，因为周围有不同的定义。 Excel定义与ISO定义不同。获取ISO周数使用(复制自http://www.rondebruin.nl/win/s8/win001.htm)

Public Function IsoWeekNumber(d As Date) As Integer

Dim d2 As Long

d2 = DateSerial(Year(d - Weekday(d - 1) + 4), 1, 3)

IsoWeekNumber = Int((d - d2 + Weekday(d2) + 5) / 7)

End Function

答案 3 :(得分：0)

使用datepart和错误解决方法来计算ISO年：

'Test 2007-12-31 should return W01Y2008

myDate = "2007-12-31"

ISOWeek = DatePart("ww", myDate, vbMonday, vbFirstFourDays)

Week1 = DatePart("ww", myDate, vbMonday, vbFirstFourDays)

Week2 = DatePart("ww", DateAdd("d", 7, myDate), vbMonday, vbFirstFourDays)

ISOYear = DatePart("yyyy", myDate, vbMonday, vbFirstFourDays)

Year1 = DatePart("yyyy", myDate, vbMonday, vbFirstFourDays)

Year2 = DatePart("yyyy", DateAdd("d", 7, myDate), vbMonday, vbFirstFourDays)

If ISOWeek = 53 And DatePart("ww", DateAdd("d", 7, myDate), vbMonday, vbFirstFourDays) = 2 Then

ISOWeek = 1

End If

if ISOWeek = 1 And DatePart("yyyy", DateAdd("d", 7, myDate), vbMonday, vbFirstFourDays) > ISOYear Then

ISOYear = ISOYear + 1

End If

MsgBox("W" & ISOWeek & "Y" & ISOYear)

' Result in W01Y2008

答案 4 :(得分：0)

所以，这是我最后的工作版本

data: {

popupMode: true,

isSelected: false

},

methods: {

selectItem() {

this.isSelected= true;

}

}

答案 5 :(得分：0)

WeekdayName( number, [abbreviate], [firstdayofweek] )

WeekdayName(2)

结果：'星期一'

WeekdayName(2, TRUE)

结果：'星期一'

WeekdayName(2, TRUE, vbMonday)

结果：'星期一'