作者:乔山办公网日期:
返回目录:excel表格制作
假设日期的复A2单元格。且是日期格制式的,公式zd如下:
=IF(WEEKNUM(A2,2)=53,YEAR(A2)+1&"01",IF(WEEKNUM(A2,2)=52,IF(WEEKDAY(A2,2)>4,YEAR(A2)+1&"01",YEAR(A2)&TEXT(WEEKNUM(A2,2),"00")),YEAR(A2)&TEXT(WEEKNUM(A2+3,2),"00")))
既然是日期相关的计算来,就尽量用日期函数自吧。如图:
B1=DATE(2010+LEFT(A1),1,1)+MID(A1,2,2)*7-6-WEEKDAY(DATE(2010+LEFT(A1),1,1))+RIGHT(A1)
公式说明见zhidao图