J2单元格填:=IF(MID(E2,12,4)-MID(E2,1,4)=0,E2,IF(MID(E2,12,4)-MID(E2,1,4)=1,MID(E2,1,10)&"-"&MID(E2,1,4)&"/12/31,"&MID(E2,12,4)&"/01/01-"&MID(E2,12,10),IF(MID(E2,12,4)-MID(E2,1,4)=2,MID(E2,1,10)&"-"&MID(E2,1,4)&"/12/31,"&MID(E2,1,4)+1&"/01/01-"&MID(E2,1,4)+1&"/12/31,"&MID(E2,12,4)&"/01/01-"&MID(E2,12,10),IF(MID(E2,12,4)-MID(E2,1,4)=3,MID(E2,1,10)&"-"&MID(E2,1,4)&"/12/31,"&MID(E2,1,4)+1&"/01/01-"&MID(E2,1,4)+1&"/12/31,"&MID(E2,1,4)+2&"/01/01-"&MID(E2,1,4)+2&"/12/31,"&MID(E2,12,4)&"/01/01-"&MID(E2,12,10),IF(MID(E2,12,4)-MID(E2,1,4)=4,MID(E2,1,10)&"-"&MID(E2,1,4)&"/12/31,"&MID(E2,1,4)+1&"/01/01-"&MID(E2,1,4)+1&"/12/31,"&MID(E2,1,4)+2&"/01/01-"&MID(E2,1,4)+2&"/12/31,"&MID(E2,1,4)+3&"/01/01-"&MID(E2,1,4)+3&"/12/31,"&MID(E2,12,4)&"/01/01-"&MID(E2,12,10),"***")))))
K2单元格填:=IF(IFERROR(FIND(MID(H2,1,4),J2),0)=0,"无",IF(SUBSTITUTE(H2,"/","",1)-SUBSTITUTE(MID(J2,1,7),"/","",1)>=0,IF(SUBSTITUTE(MID(RIGHT(J2,10),1,7),"/","",1)-SUBSTITUTE(H2,"/","",1)>=0,MID(J2,FIND(MID(H2,1,4),J2),21),"无"),"无"))
然后下拉。 |