【函中20130301】第十一讲D组作业上交帖

顺⑦.zì繎。

1、本贴已设置回贴仅作者可见，直接跟贴回复即可；
2、上交时直接贴公式，只上传附件的不予批改；
3、答题时必须注明【函中201301班】-组号-论坛ID,如【函中201301班】-C99-CheryBTL；
4、截止时间2013年7月19日17:00；
5、请误重复占楼，非本组同学跟贴回复者扣分。
下附课程相关链接:
函中201301班第十一讲课件

cg372101

【函中201301班】-D18-cg372101
题1：

1. =TEXT(SUM(--TRIM(MID(SUBSTITUTE(B303," ",REPT(" ",20)),{1,20},{21,99}))),"e年mm月dd日上午/下午hh时ss分mm秒")

复制代码

题2：

1. =MONTH(1&B313)

复制代码

题3：

1. =--SUBSTITUTE(RIGHT(SUBSTITUTE(C322,"/","   /   "),4)&MID(SUBSTITUTE(C322,"/","   /   "),3,14)&LEFT(SUBSTITUTE(C322,"/","   /   "),2)," ",)

复制代码

题4：

1. =TEXT(C337+4-WEEKDAY(C337,14),"e-m-d")

复制代码

题5：

1. =MONTH(TEXT(SUBSTITUTE(C346,".","-"),"[>1231]m-d;0-00"))

复制代码

题6：

1. =TEXT((C$360&"-1-1")+7*(D$360-1)+ROW(A1)-WEEKDAY(C$360&"-1-1",2),"m/d")

复制代码

liziyuliziyu7

函中201301班】-d04-liziyuliziyu7

=TEXT(LEFT(B303,FIND(" ",B303)),"yyyy年mm月dd日")&TEXT(TRIM(MID(B303,FIND(" ",B303)+1,99)),"hh时mm分ss秒")
=MONTH(B313&1)
=TEXT(TRIM(MID(C322,FIND("/",C322,4)+1,4))&"-"&MID(C322,FIND("/",C322)+1,FIND("/",C322,4)-FIND("/",C322)-1)&"-"&LEFT(C322,FIND("/",C322)-1),"e-m-d")
=IF(WEEKDAY(C337,2)>=4,C337-WEEKDAY(C337,2)+7,C337-WEEKDAY(C337,2))
=IF(AND(ISNUMBER(C346),C346>10000),MONTH(C346),IF(ISNUMBER(--LEFT(C346,2)),IF(--LEFT(C346,2)<13,--LEFT(C346,2),LEFT(C346)),LEFT(C346)))
=DATE(C$360,1,ROW(A2))+(D$360-1)*WEEKDAY(C$360&"-1-1",2)

veryloveep

【函中201301班】-D22-veryloveep
1 =TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B303,"年","-"),"月","-"),"日",),"时",":"),"分",":"),"秒",),"[$-409]yyyy年m月d日上午/下午!0h时mm分ss秒")
2 =MONTH(B313&"-1")
3 =--(RIGHT(C322,LEN(C322)-FIND("/",C322,FIND("/",C322)+1))&"-"&MID(C322,FIND("/",C322)+1,FIND("/",C322,FIND("/",C322)+1)-FIND("/",C322)-1)&"-"&LEFT(C322,FIND("/",C322)-1))
4=IF(WEEKDAY(C337,2)>3,7-WEEKDAY(C337,2)+C337,-WEEKDAY(C337,2)+C337)

笨笨四

【函中201301班】-D09-笨笨四
1。=TEXT(SUM(--TRIM(MID(SUBSTITUTE(B303," ",REPT(" ",99)),{1,99},{99,999}))),"e年mm月dd日上午/下午hh时mm分ss秒")
2。=MONTH(B313&0)
3。=TEXT(TEXT(SUM(MID(SUBSTITUTE(C322,"/",REPT(" ",9)),{19,10,1},9)*10^{4,2,0}),"#-00-00"),"e-m-d")
4。=C337+7*(WEEKDAY(C337)>4)-WEEKDAY(C337)+1
5。=TEXT(TEXT(C346,"[>1231]0;[<13]0!-1;0-00"),"m")
6。=TEXT((C$360&-1)+7*D$360+ROW(A1)-7-WEEKDAY(C$360&-1,2),"m/d")

林玲玲

【函中201301班】-D11-林玲玲
1、=TEXT(LEFT(B303,FIND(" ",B303))+SUBSTITUTE(MID(B303,FIND(" ",B303),20)," ",),"e年mm月dd日上午/下午hh时mm分ss秒")
2、=MONTH(B313&1)
3、=TEXT(--SUBSTITUTE(SUBSTITUTE(C322,"/","  "),MID(SUBSTITUTE(C322,"/","  "),3,4),TEXT(--MID(SUBSTITUTE(C322,"/","  "),3,4)&"-1","mmm")),"e-m-d")
4、=TEXT(IF(MOD(C337-2,7)+1<=3,C337-MOD(C337-2,7)-1,C337+6-MOD(C337-2,7)),"e-m-d")
5、=MONTH(IF(--SUBSTITUTE(C346,".","-")<40000,TEXT(C346,"0-00"),SUBSTITUTE(C346,".","-")))
6、=TEXT((C360&"-1")+(D360-1)*7,"M/D")

1982zyh

【函中201301班】-D16-1982zyh
1.=TEXT(IF(ISNUMBER(--LEFT(B303,FIND(" ",B303)-1)),--LEFT(B303,FIND(" ",B303)-1),--(MID(B303,1,FIND("年",B303)-1)&"-"&MID(B303,FIND("年",B303)+1,FIND("月",B303)-FIND("年",B303)-1)&"-"&MID(B303,FIND("月",B303)+1,FIND("日",B303)-FIND("月",B303)-1))),"yyyy年m月d日")&TEXT(IF(ISNUMBER(--(MID(TRIM(B303),FIND(" ",TRIM(B303))+1,LEN(B303)-FIND(" ",TRIM(B303))))),--(MID(TRIM(B303),FIND(" ",TRIM(B303))+1,LEN(B303)-FIND(" ",TRIM(B303)))),IF(RIGHT(B303,1)="秒",--(MID(TRIM(B303),FIND(" ",TRIM(B303))+1,FIND("时",TRIM(B303))-FIND(" ",TRIM(B303))-1)&":"&MID(TRIM(B303),FIND("时",TRIM(B303))+1,FIND("分",TRIM(B303))-FIND("时",TRIM(B303))-1)&":"&MID(TRIM(B303),FIND("分",TRIM(B303))+1,FIND("秒",TRIM(B303))-FIND("分",TRIM(B303))-1)),--(MID(TRIM(B303),FIND(" ",TRIM(B303))+1,FIND("时",TRIM(B303))-FIND(" ",TRIM(B303))-1)&":"&MID(TRIM(B303),FIND("时",TRIM(B303))+1,FIND("分",TRIM(B303))-FIND("时",TRIM(B303))-1)))),"AM/PMHh时mm分ss秒")
2.=MONTH(--(PROPER(B313)&"-1"))
3.=DATE(IF(--(MID(MID(C322,FIND("/",C322)+1,LEN(C322)),FIND("/",MID(C322,FIND("/",C322)+1,LEN(C322)))+1,4))<2000,MID(MID(C322,FIND("/",C322)+1,LEN(C322)),FIND("/",MID(C322,FIND("/",C322)+1,LEN(C322)))+1,4)+2000,MID(MID(C322,FIND("/",C322)+1,LEN(C322)),FIND("/",MID(C322,FIND("/",C322)+1,LEN(C322)))+1,4)),MID(C322,FIND("/",C322)+1,FIND("/",MID(C322,FIND("/",C322)+1,LEN(C322)))-1),LEFT(C322,FIND("/",C322)-1))
4.=TEXT(IF(WEEKDAY(C337,2)>3,C337+7-WEEKDAY(C337,2),C337-WEEKDAY(C337,2)),"yyyy-m-d")
5.=TEXT(IF(FIND("月",C346&"月")<LEN(C346),LEFT(C346,FIND("月",C346)-1),IF(FIND(".",C346&".")<LEN(C346),LEFT(C346,FIND(".",C346)-1),IF(LEN(C346)<5,LEFT(C346,LEN(C346)-2),MONTH(C346)))),"0")
6.=TEXT(--(DATE($C$360,1,1)+WEEKDAY(YEAR($C$360),2)*8+ROW(A1)),"m/d")

lucking551

【函中201301班】-D05-lucking551
第一题：

1. =TEXT(--(LEFT(B303,FIND(" ",B303))),"yyyy年mM月dD日")&TEXT(SUBSTITUTE((RIGHT(B303,LEN(B303)-FIND(" ",B303)))," ",),"上午/下午Hh时mm分ss秒")

复制代码

第二题：

1. =MONTH(--(B313&24))

复制代码

第三题：

1. =--(--RIGHT(C322,LEN(C322)-FIND("/",C322,4))&"/"&--MID(C322,FIND("/",C322)+1,LEN(C322)-(LEN(C322)-FIND("/",C322,4))-FIND("/",C322)-1)&"/"&--LEFT(C322,FIND("/",C322)-1))

复制代码

第四题：

1. =IF(MOD(C337-1,7)>3,7-MOD(C337-1,7)+C337,C337-MOD(C337-1,7))

复制代码

第五题：

1. =MONTH(IF(LEN(TEXT(SUBSTITUTE(C346,".","/"),"0000"))=4,--(LEFT(TEXT(SUBSTITUTE(C346,".","/"),"0000"),2)&"/"&RIGHT(TEXT(SUBSTITUTE(C346,".","/"),"0000"),2)),--TEXT(SUBSTITUTE(C346,".","/"),"0000")))

复制代码

第六题：

1. =($C$360&"-1-1")+8-WEEKDAY(($C$360&"-1-1")*1,2)+($D$360-2)*7+ROW(A1)-1

复制代码

lion_zjy

【函中201301班】-D10-lion_zjy
1.=TEXT(LEFT(B303,SEARCH(" ",B303)-1)+TRIM(RIGHT(B303,LEN(B303)-SEARCH(" ",B303))),"yyyy年mm月dd日"&"上午/下午hh""时""mm""分""ss""秒""")

2.=MONTH(B313&1)

3.=--TEXT(--MID(SUBSTITUTE(C322,"/",REPT(" ",9)),20,9)&-MID(SUBSTITUTE(C322,"/",REPT(" ",9)),10,9)&-MID(SUBSTITUTE(C322,"/",REPT(" ",9)),1,9),"yyyy-mm-dd")

4.=IF(WEEKDAY(C337,2)<4,C337-WEEKDAY((C337),2),C337-WEEKDAY(C337,2)+7)

5.=MONTH(TEXT(SUBSTITUTE(C346,".","-"),"[<10000]0-00")*1)

6.=TEXT(DATE(C$360,1,1)+ROW(A64)-WEEKDAY(DATE(C$360,1,1),2),"m/d")

yunqingzi

1、=TEXT(MID(B303,1,FIND(" ",B303)),"e年mm月dd日")&TEXT(SUBSTITUTE(RIGHT(B303,FIND(" ",B303))," ",""),"上午/下午hh时mm秒ss分")

2、=MONTH(B313&5)

3、=TEXT(RIGHT(SUBSTITUTE(C322,"/","-",1),LEN(C322)-FIND("/",SUBSTITUTE(C322,"/","-",1),1))&"/"&MID(C322,FIND("-",SUBSTITUTE(C322,"/","-",1))+1,FIND("/",SUBSTITUTE(C322,"/","-",1))-1-FIND("-",SUBSTITUTE(C322,"/","-",1)))&"/"&LEFT(SUBSTITUTE(C322,"/","-",1),FIND("-",SUBSTITUTE(C322,"/","-",1))-1),"yyyy/m/d")

4、=IF(WEEKDAY(C337,2)<=3,C337-WEEKDAY(C337,2),C337-WEEKDAY(C337,2)+7)

5、

6、=IF(WEEKNUM(TEXT(DATE(C$360,1,1)+(D$360*7)-14+ROW(1:1),"e-m-d"),2)<>D$360,"",TEXT(DATE(C$360,1,1)+(D$360*7)-14+ROW(1:1),"m/d"))