第1个回答 推荐于2020-03-22
1、按照每月计算周
=YEAR(A1) & "年" & MONTH(A1) & "月" & "第" & INT(DAY(A1)/7+1) & "周"
2、按照日历计算周
=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))
3、按照日历计算周(区别上年最后一周(0),本年最后一周(52))
=ROUNDUP((A1-DATE(YEAR(A1),1,)+MOD(WEEKDAY(DATE(YEAR(A1),1,),2),7))/7,0)-1
第3个回答 2010-01-27
如果以星期日为一个星期的第1天,公式可以这样:
=YEAR(A1)&"年"&MONTH(A1)&"月第"&IF(DAY(A1)<=7-WEEKDAY(DATE(YEAR(A1),1,1),2),1,ROUNDUP((DATEDIF(DATE(YEAR(A1),1,1),A1+1,"yd")-(7-WEEKDAY(DATE(YEAR(A1),1,1),2)))/7,0)+1)&"周"