请教关于星期的计算？

rf

如何通过输入一个日期：2003-10-20 即可得到该天在本年度的第几个星期？ [em00] [em00] [em00] [em00] [em00] [em02] [em02] [em02] [em02] [em02]

sunnet

使用 WEEKNUM 函数。 如： =WEEKNUM(A1) =WEEKNUM(TODAY()) 请注意，使用此函数需要加载「分析工具库」。

rf

还有没有其他的办法?

Emily

以下是引用rf在2003-11-3 10:56:00的发言： 如何通过输入一个日期：2003-10-20 即可得到该天在本年度的第几个星期？ [em00] [em00] [em00] [em00] [em00] [em02] [em02] [em02] [em02] [em02]

'Under the ISO standard, a week always begins on a Monday, and ends on a Sunday. 'The first week of a year is that week which contains the first Thursday of the year, 'or, equivalently, contains Jan-4. ' Public Function ISOWeekNum(AnyDate As Date, _ Optional WhichFormat As Variant) As Integer ' ' WhichFormat: missing or <> 2 then returns week number, ' = 2 then YYWW ' Dim ThisYear As Integer Dim PreviousYearStart As Date Dim ThisYearStart As Date Dim NextYearStart As Date Dim YearNum As Integer ThisYear = Year(AnyDate) ThisYearStart = YearStart(ThisYear) PreviousYearStart = YearStart(ThisYear - 1) NextYearStart = YearStart(ThisYear + 1) Select Case AnyDate Case Is >= NextYearStart ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1 YearNum = Year(AnyDate) + 1 Case Is < ThisYearStart ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1 YearNum = Year(AnyDate) - 1 Case Else ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1 YearNum = Year(AnyDate) End Select If IsMissing(WhichFormat) Then Exit Function End If If WhichFormat = 2 Then ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & _ Format(ISOWeekNum, "00")) End If End Function Public Function YearStart(WhichYear As Integer) As Date Dim WeekDay As Integer Dim NewYear As Date NewYear = DateSerial(WhichYear, 1, 1) WeekDay = (NewYear - 2) Mod 7 If WeekDay < 4 Then YearStart = NewYear - WeekDay Else YearStart = NewYear - WeekDay + 7 End If End Function

fieldsun

日期在a1 =INT((A1-DATE(YEAR(A1),1,0)+WEEKDAY(DATE(YEAR(A1),1,0),1)+7-WEEKDAY(A1,1))/7)