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

石岗

【函中201301班】-A23-石岗
第一题：=TEXT(TEXT(LEFT(B303,FIND(" ",B303)),"yyyy-mm-dd"),"yyyy年mm月dd日;@")&TEXT(SUBSTITUTE(MID(B303,FIND(" ",B303)+1,20)," ",""),"上午/下午h时mm分ss秒;@")；
第二题：=VLOOKUP(B313,{"JAN",1;"FEB",2;"MAR",3;"APR",4;"MAY",5;"JUN",6;"JUL",7;"AUG",8;"SEP",9;"OCT",10;"NOV",11;"DEC",12},2,FALSE)；
第三题：=TEXT(SUBSTITUTE(RIGHT(SUBSTITUTE(C322,"/","    "),4)&"-"&MID(SUBSTITUTE(C322,"/","    "),4,7)&"-"&LEFT(SUBSTITUTE(C322,"/","    "),2)," ",""),"yyyy-m-d")；
第四题：=IF(WEEKDAY(TEXT(C337,"0"),2)<=3,C337-WEEKDAY(TEXT(C337,"0"),2),IF(WEEKDAY(TEXT(C337,"0"),2)<7,C337+IF(WEEKDAY(TEXT(C337,"0"),2)<=3,WEEKDAY(TEXT(C337,"0"),2)*7,IF(WEEKDAY(TEXT(C337,"0"),2)<7,7-WEEKDAY(TEXT(C337,"0"),2),0)),C337))；
第五题：=MONTH(IF(LEN(-SUBSTITUTE(C346,".","-"))=6,SUBSTITUTE(C346,".","-"),TEXT(C346,"0!-00")))；
第六题：=TEXT(IF(WEEKDAY(--($C$360&"-1-1")+$D$360*7,2)=1,--($C$360&"-1-1")+($D$360-1)*7,--($C$360&"-1-1")+($D$360-1)*7-WEEKDAY(--($C$360&"-1-1")+$D$360*7,2)+ROW(A1)),"m/d")
请指正。

dididing

【函中201301班】-A18-dididing

因工作出差，所以本次作业只做了三个题。就这样吧，其他的，其实我也不会了。

第一题：

1. =TEXT(LEFT(SUBSTITUTE(B303," ",REPT(" ",8)),11),"e年mm月dd日")&TEXT(TRIM(RIGHT(SUBSTITUTE(B303," ",REPT(" ",8)),9)),"上午/下午hh时mm分ss秒")
   

复制代码

第二题 ：

1. =MONTH(B313&-1)

复制代码

第四题：

1. =CHOOSE(WEEKDAY(C337),0,-1,-2,-3,3,2,1)+C337
   

复制代码

as0810114

201301函中班-A12-as0810114
1.   =TEXT(LEFT(B303,FIND(" ",B303))+RIGHT(B303,LEN(TRIM(B303))-FIND(" ",B303)),"yyyy年mm月dd日上午/下午hh时mm分ss秒")
2.   =MONTH(B313&1)
3.   =TEXT(TEXT(SUBSTITUTE(C322,20,),"d/m/y"),"yyyy-m-d")
4.  =C337-WEEKDAY(C337)+1+7*(WEEKDAY(C337)>4)
5.  =MONTH(TEXT(SUBSTITUTE(C346,".","-"),"[>1231]0;0-00"))
6.  =TEXT(DATE(C$360,1,ROW(A1)-6-WEEKDAY(DATE(C$360,1,1),2)+7*D$360),"m/d")

风林火山

1

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

复制代码

2

1. =TEXT(C313&"-1","M")

复制代码

3

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

复制代码

4

1. =TEXT(C337-WEEKDAY(C337,2)+(WEEKDAY(C337,2)>3)*7,"e-m-d")

复制代码

5

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

复制代码

6

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

复制代码