【函中20130301】第十一讲C组作业上交帖【开贴】

CheryBTL

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

下附课程相关链接:
函中201301班第十一讲课件

xiaoni

【函中201301班】-C15-xiaoni上交第11课作业，不好意思，只会做2题
第一题：=TEXT(LEFT(B304,FIND(" ",B304)-1),"e年mm月dd日")&TEXT(TRIM(MID(B304,FIND(" ",B304),55)),"上午/下午hh时mm分ss秒")
第二题：=MONTH(--(B313&-2013))

kouhogliang

【函中201301班】-C08-kouhogliang

1

1. =TEXT(LEFT(B303,FIND(" ",B303)-1),"yyyy年mm月dd日")&TEXT(SUBSTITUTE(MID(B303,FIND(" ",B303)+1,99)," ",""),"上午/下午hh时mm分ss秒;@")

复制代码

2

1. =SUBSTITUTE(MONTH("1-"&B313),"1-","")

复制代码

3

1. =--TEXT(SUBSTITUTE(C322,"/20","/"),"d-m-y")

复制代码

4

1. =TEXT(IF(WEEKDAY(C337)<5,C337+1-WEEKDAY(C337,1),C337+8-WEEKDAY(C337,1)),"yyyyy-m-d")

复制代码

5

1. =MONTH(SUBSTITUTE(IF(AND(C346<9999,C346>99),C346/100,C346),".","-"))

复制代码

6.选中e360到e366

1. =TEXT((C360&"-1-1")-WEEKDAY((C360&"-1-1"),2)+(D360-1)*7+{1;2;3;4;5;6;7},"m/d")

复制代码

三键

qqyyh

【函中201301班】-C06-qqyyh
1.=TEXT(LEFT(B303,FIND(" ",B303)-1),"yyyy年mm月dd日")&TEXT(TRIM(MID(B303,FIND(" ",B303)+1,15)),"上午/下午hh时mm分ss秒") 下拉
2.=TEXT(B313&1,"m") 下拉
  =MATCH(B313,{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},) 下拉
3.=--(TEXT(MID(C322,FIND("/",C322,FIND("/",C322)+1)+1,4),"[>2000]0;[<2000]2000")&"/"&MID(C322,FIND("/",C322)+1,FIND("/",C322,FIND("/",C322)+1)-FIND("/",C322)-1)&"/"&LEFT(C322,FIND("/",C322)-1)) 下拉
4.=TEXT(IF(WEEKDAY(C337,2)<4,C337-MOD(C337-1,7),IF(WEEKDAY(C337,2)<7,C337+7-WEEKDAY(C337,2),C337)),"yyyy-m-d") 下拉
5.=MONTH(IF(--SUBSTITUTE(C346,".","-")>40000,TEXT(SUBSTITUTE(C346,".","-"),"yyyy-m-d"),TEXT(C346,"2013-00-00")))下拉
6.=DATE(1956,1,1)+WEEKDAY((1956-1-1),2)+9*7-7-(7-ROW(A1)) 下拉
  =DATE(1956,1,1)+9*7-(7-ROW(A1)) 下拉

csmccdh

【函中201301班】-C01-csmccdh
第一题：=TEXT(--LEFT(B303,FIND(" ",B303)-1),"e年mm月dd日")&TEXT(--TRIM(MID(B303,FIND(" ",B303),99)),"上午/下午hh时mm分ss秒")

第二题：=MONTH(--(B313&1))

第三题：=TEXT(--TEXT(SUBSTITUTE(C322,20,),"d-m-y"),"e-m-d")

第四题：=C337-WEEKDAY(C337,2)+(WEEKDAY(C337,2)>3)*7

第五题：=TEXT(IF(-C346<-1231,C346,TEXT(C346*IF(MOD(C346,1),100,1),"#-00")),"m")

第六题：=TEXT(DATE(C$360,1,)-WEEKDAY(DATE(C$360,1,),2)+D$360*7-7+ROW(A1),"m/d")

lisa19860622

函中20130301-c19-lisa19860622
题1:

1. =TEXT(LEFT(B303,FIND(" ",TRIM(B303))-1),"e年mm月dd日")&TEXT(MID(TRIM(B303),FIND(" ",TRIM(B303))+1,99),"上午/下午hh时mm分ss秒")

复制代码

题2:

1. =MATCH(B313,TEXT("01-"&ROW($1:$12)&"-1","mmm"),)

复制代码

题3:

1. =TEXT(SUBSTITUTE(MID(C322,FIND("/",C322,FIND("/",C322)+1)+1,99)&"/"&MID(C322,FIND("/",C322)+1,2)&"/"&MID(C322,1,FIND("/",C322)-1),"//","/"),"e/m/d")

复制代码

题4:

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

复制代码

题5:

1. =(IF(NOT((--C346)<1232),TEXT(C346,"m"),--MID(C346,1,LEN(C346)-2)))&""

复制代码

题6:

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

复制代码

zhs5483

【函中201301班】-C11-zhs5483
1、=TEXT(MID(B303,1,FIND(" ",B303)),"yyyy年mm月dd日")&TEXT(MID(TRIM(B303),FIND(" ",TRIM(B303))+1,15),"上午/下午h时mm分ss秒")

2、=TEXT(--(B313&"-1"),"m")

3、=DATE(IF(LEN(C322)-FIND("/",C322,4)=1,"200"&RIGHT(C322,1),"20"&RIGHT(C322,2)),MID(C322,FIND("/",C322)+1,FIND("/",C322,4)-(FIND("/",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、=IF(OR(ISTEXT(C346),C346>10^4),TEXT(C346,"m"),MID(TEXT(C346,"[<12]#;##-##"),1,FIND("-",TEXT(C346,"[<12]#;##-##")&"-")-1))

6、

wangzan

第一题：=TEXT(--MID(SUBSTITUTE(B303," ",REPT(" ",15)),1,15),"yyyy年mm月dd日")&TEXT(TIMEVALUE(MID(SUBSTITUTE(B303," ",REPT(" ",15)),16,50)),"上午/下午h时mm分ss秒")
第二题：=MATCH(B313,TEXT(2013&"-"&ROW($1:$12),"mmm"),0)
第三题：=TEXT(DATEVALUE(MID(SUBSTITUTE(C322,"/",REPT(" ",15)),32,15)&-MID(SUBSTITUTE(C322,"/",REPT(" ",15)),16,15)&-MID(SUBSTITUTE(C322,"/",REPT(" ",15)),1,15)),"e/m/d")
第四题：=IF(MOD(C337-1,7)>3,C337+7-MOD(C337-1,7),C337-MOD(C337-1,7))
第五题：=MONTH(IF(--SUBSTITUTE(C346,".","-")<1231,--(MID(C346,1,LEN(C346)-2)&"-"&RIGHT(C346,2)),--SUBSTITUTE(C346,".","-")))
第六题：=DATE($C$360,1,1)-IF(MOD($G$360-1,7)=0,7,MOD($G$360-1,7))+ROW(A1)+($D$360-1)*7
【函中201301班】-C09-wangzan

雨后的风

【函中201301班】-C10-雨后的风
第一题

1. =TEXT(LEFT(B303,FIND(" ",B303))+TRIM(MID(B303,FIND(" ",B303),99)),"e年mm月dd日上午/下午hh时mm分ss秒")

复制代码

1. =TEXT(LEFT(B303,FIND(" ",B303)),"e年mm月dd日")&TEXT(TRIM(MID(B303,FIND(" ",B303),99)),"上午/下午hh时mm分ss秒")

复制代码

第二题

1. =MONTH(B313&1)

复制代码

1. =TEXT(B313&1,"m")

复制代码

第三题

1. =--TEXT(SUM(MID(SUBSTITUTE(C322,"/",REPT(" ",9)),{1,10,19},9)*10^{0,2,4}),"0-00-00")

复制代码

1. =TEXT(SUBSTITUTE(REPLACE(C322,3,FIND("/",C322),TEXT(2013&-SUBSTITUTE(MID(C322,FIND("/",C322)+1,2),"/",),"MMM")),"/",),"e-m-d")

复制代码

第四题

1. =C337+(WEEKDAY(C337,2)>3)*7-WEEKDAY(C337,2)

复制代码

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

复制代码

第五题

1. =TEXT(C346,"[>2000]m;"&LEFT(--C346,LEN(--C346)-2))

复制代码

1. =IF(C346<2000,--LEFT(C346,LEN(C346)-2),MONTH(C346))

复制代码

第六题

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

复制代码

xuyouxm

【函中201301班】-C21-xuyouxm

01:

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

复制代码

02:

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

复制代码

03:

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))

复制代码

04:

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

复制代码

05:

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")))

复制代码

06:

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

复制代码