æ¹æ³ä¸ï¼
=IF(AND(WEEKDAY(DATE(YEAR(A1),1,1),2) > 1,FLOOR(DATEDIF(DATE(YEAR(A1),1,1),A1+WEEKDAY(DATE(YEAR(A1),1,1)-8,2),"D")/7,1)=0), FLOOR(DATEDIF(DATE(YEAR(A1)-1,1,1),DATE(YEAR(A1),1,1)+WEEKDAY(DATE(YEAR(A1)-1,1,1)-8,2),"D")/7,1), FLOOR(DATEDIF(DATE(YEAR(A1),1,1),A1+WEEKDAY(DATE(YEAR(A1),1,1)-8,2),"D")/7,1))
å
¶ä¸A1æ¯è¦è®¡ç®çåå
æ ¼ï¼å¦å¤è¿ä¸ªæ¯æç
§ä¸å½çä¹ æ¯ï¼ä¹å°±æ¯è¯´ä¸å¨æ¯ä»ææä¸å¼å§å°æææ¥ç»æçï¼å¦æè¦æç
§ç¾å½çä¹ æ¯ï¼åªè¦å°WEEKDAYç第2个åæ°æ¹æ1å°±è¡äºã
æ¹æ³äºï¼
=INT(((A2-VALUE(YEAR(A2)&-1&-1))+WEEKDAY(VALUE(YEAR(A2)&-1&-1))-1)/7)
æ¹æ³ä¸ï¼å½æ°WEEKNUM(serial_num,return_type)è¿åä¸ä¸ªæ¥æå¨è¯¥å¹´ä¸ç¬¬å å¨çæ°åãå
¶ä¸åæ°return_type为1表示ä»æææ¥å¼å§è³ææå
ç®ä¸å¨ï¼ä¸º2表示ä»ææä¸å¼å§è³æææ¥ç®ä¸å¨ã å¦B1åå
æ ¼å
å«æ¥æå¼2006-1-2ï¼ä¸é¢çå
¬å¼åå«è¿åæ°å1å2ï¼ä»£è¡¨ç¬¬ä¸å¨å第äºå¨ã2006-1-1æ¯æææ¥ï¼è¥return_type为1åä»2006-1-1è³2006-1-7æ¥ä¸ºç¬¬ä¸å¨ï¼è¥return_type为2å2006-1-1为第ä¸å¨ï¼ä»2006-1-2è³2006-1-8为第2å¨ã=WEEKNUM(B1,1)=WEEKNUM(B1,2) 注æ: å
å è½½"åæå·¥å
·åº"æè½ä½¿ç¨WEEKNUMå½æ°ãå è½½çæ¹æ³æ¯åå»èå"å·¥å
·"ï¼"å è½½å®"ï¼å¨"å è½½å®"对è¯æ¡ä¸å¾é"åæå·¥å
ጼ"
å¤éæ¡ï¼æååå»"ç¡®å®"æé®ã