|
Q:求某天是当年第几周?(相当于WEEKNUM函数)
A:
星期一起始
1:=-INT(-(B3-(YEAR(B3)&-1&-1)+WEEKDAY((YEAR(B3)&-1&-1),2))/7)
=-INT(((YEAR(B3)&-1&-1)-B3-WEEKDAY((YEAR(B3)&-1&-1),2))/7)(简化)
2:=-INT(-(B3-TEXT(B3,"e-!1")+WEEKDAY(TEXT(B3,"e-!1"),2))/7)
=-INT((TEXT(B3,"e-!1")-B3-WEEKDAY(TEXT(B3,"e-!1"),2))/7)(简化)
3:=-INT(-(B3-FLOOR((YEAR(B3)&-1&-1),7)+1)/7)
=-INT((FLOOR((YEAR(B3)&-1&-1),7)-B3-1)/7)(简化)
4:=-INT(-(B3-FLOOR(TEXT(B3,"e-!1"),7)+1)/7)
=-INT((FLOOR(TEXT(B3,"e-!1"),7)-B3-1)/7) (简化)
星期日起始
5:=-INT(-(B3-FLOOR((YEAR(B3)&-1&-1),7))/7)
=-INT((FLOOR((YEAR(B3)&-1&-1),7)-B3)/7)(简化)
6:=-INT(-(B3-FLOOR(TEXT(B3,"e-!1"),7))/7)
=-INT((FLOOR(TEXT(B3,"e-!1"),7)-B3)/7) (简化)
[ 本帖最后由 一指禅 于 2010-4-7 20:22 编辑 ] |
|