# Excel常见问题：关于星期的计算

2015-04-14 18:04 阅读 63 次 评论关闭

Excel常见问题关于星期计算

=WEEKNUM(TODAY())

=INT((A1-DATE(YEAR(A1),1,0)+WEEKDAY(DATE(YEAR(A1),1,0),1)+7-WEEKDAY(A1,1))/7)

'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