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

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

Excel常见问题关于星期计算

如何通过输入一个日期:2003-10-20即可得到该天在本年度的第几个星期?

解答:

使用 WEEKNUM 函数。
如:=WEEKNUM(A1)
=WEEKNUM(TODAY())

或者:

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

也可以用VBA:
'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

版权声明:本文著作权归原作者所有,欢迎分享本文,谢谢支持!
转载请注明:Excel常见问题:关于星期的计算 | 猎微网
分类:Excel 标签:, , ,

评论已关闭!