[201401函中]E组第七讲作业提交贴

chh0926

1
=MROUND(B4-1,7)+1
2
=ROUNDDOWN(DATE(B14,1,1)/7+C14,0)*7-5
3
=ROUNDUP((DAY(EOMONTH(TODAY(),0))-MOD(7-MOD(EOMONTH(TODAY(),-1)+1,7),7))/7,0)
4
=MONTH(B25)+(DAY(B25)>24)-(MONTH(B25)=12)*(DAY(B25)>24)*12
5
=MONTH("1-"&B33)
=TEXT(ROUNDUP(C33/3,0),"第[DBNum1]0季度")

瑾瑄

1.=B4+IF(WEEKDAY(B4,2)>=4,7-WEEKDAY(B4,2),-WEEKDAY(B4,2))
2.=TEXT(DATE(2014,1,1)+C14*6,"YYYY-M-D")+1
3.=SUMPRODUCT(N(WEEKDAY("10-"&ROW(INDIRECT("1:"&DAY(EOMONTH(NOW(),0)))),2)=6))
   =SUMPRODUCT(N(MOD("10-"&ROW(INDIRECT("1:"&DAY(EOMONTH(NOW(),0)))),7)=0))
4.=IF(MONTH(B25)+INT(DAY(B25)/25)>12,1,MONTH(B25)+INT(DAY(B25)/25))
    =--TEXT(MONTH(B25)+INT(DAY(B25)/25),"[>12]!1;!0;0")
5.=--TEXT(B33&"-1","m")
   =TEXT(LEN(2^TEXT(B33&"-1","m")),"第[dbnum1]0季度")
   =TEXT(INT((TEXT(B33&"-1","m")+2)/3),"第[dbnum1]0季度")
   =TEXT(ROUNDUP(TEXT(B33&"-1","m")/3,),"第[dbnum1]0季度")
   =TEXT(MONTH(--TEXT(B33&"-1","m")&0),"第[dbnum1]0季度")
   =LOOKUP(--TEXT(B33&"-1","m"),{1,4,7,10},"第"&{"一","二","三","四"}&"季度")

ldxhzy

1、C4数组公式：（下拉）
           =B4+IF(WEEKDAY(B4)>4,8-WEEKDAY(B4),1-WEEKDAY(B4))
2、
3、C18公式：
     =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(YEAR(TODAY()),MONTH(TODAY()),1) & ":" & DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1)))=7))
4、C25公式：（下拉）
     =MONTH(B25)+(DAY(B25)>24)-12*((MONTH(B25)+(DAY(B25)>24))>12)

tt89004808

1. 1.=IF(ABS(B4-WEEKDAY(B4)+1-B4)<=ABS(B4-WEEKDAY(B4)+8-B4),B4-WEEKDAY(B4)+1,B4-WEEKDAY(B4)+8)
   
2. 2.=DATE(B14,1,1)-WEEKDAY(DATE(B14,1,1),2)-6+C14*7
   
3. 3.=DAY(EOMONTH(NOW(),0))-NETWORKDAYS.INTL(EOMONTH(NOW(),-1)+1,EOMONTH(NOW(),0),17)
   
4. 4.=MONTH(B25)+IF(DAY(EOMONTH(B25,0))-DAY(B25)>DAY(EOMONTH(B25,0))-25,0,1)
   
5. 5.=MONTH(B33&-1)
   
6. =TEXT(ROUNDUP(E33/3,0),"第[DBNum1][$-804]G/通用格式季度")

复制代码

xiaox0124

1. =(B4-WEEKDAY(B4,2))+7*IF(WEEKDAY(B4,2)<4,0,1)
   
2. =IF(WEEKDAY(B14&"-1-1",2)=1,(B14&"-1-1")+7*(C14-1),(B14&"-1-1")-WEEKDAY(B14&"-1-1",2)+1+7*(C14-1))
   
3. =4+IF((WEEKDAY(EOMONTH(TODAY(),-1)+1,2)-1+MOD(DAY(EOMONTH(TODAY(),0)),7))<6,0,1)
   
4. =IF(DAY(B25)<25,MONTH(B25),MONTH(EOMONTH(B25,1)))
   
5. =MONTH(B33&"-1")
   
6. ="第"&TEXT(INT((MONTH(B33&"-1")+2)/3),"[DBNum1][$$-804]G/通用格式")&"季度"

复制代码

滴答滴

1、
=B4-WEEKDAY(B4,2)+7*(WEEKDAY(B4,2)>3)
=MROUND(B4-1,7)+1

2、
=(B14&-1)+7*C14-6-WEEKDAY(B14&-1,2)
=(B14&-1)+7*C14-7-WEEKDAY(B14&-1,3)
=FLOOR((B14&-1)-2,7)+7*C14-5

3、
=DAY(EOMONTH(TODAY(),0))-NETWORKDAYS.INTL("10-1","10-31",17)
=COUNT(0/(WEEKDAY(MONTH(NOW())&-ROW(1:31))=7))
=COUNT(0/(MOD(MONTH(NOW())&-ROW(1:31),7)=0))

4、
=MOD(MONTH(B25)+(DAY(B25)>24)-1,12)+1
=MONTH(EOMONTH(B25,N(DAY(B25)>24)))

5、
=MONTH(B33&-1)
=MONTH(B33&1)
=TEXT(MONTH(B33&-1)*10,"[dbnum1]第M季度")

满坛皆为吾师

1

1. =B4+(WEEKDAY(B4,2)>3)*7-WEEKDAY(B4,2)
   
2. =1+ROUND((B4-1)/7,)*7

复制代码

2

1. =2+(C14-1+ROUND(("2014-1-1"-1)/7,))*7
   
2. =ROUNDUP((DATE(B14,1,1)-2)/7,)*7+2+(C14-2)*7

复制代码

3

1. =COUNT(0/(WEEKDAY(TEXT(NOW(),"e-m")&-ROW(1:31),2)=6))
   

复制代码

4

1. =IF(B25>41997,1,MONTH(B25)+(DAY(B25)>24))
   
2. =MONTH(EOMONTH(B25,DAY(B25)/25))

复制代码

5

1. =MONTH(C33&-1)
   
2. =TEXT(LEN(2^E33),"[DBnum1]第0季度")

复制代码

天龙九部

1.IF(ABS(7-WEEKDAY(B4,2))<ABS(-WEEKDAY(B4,2)),7-WEEKDAY(B4,2)+B4,-WEEKDAY(B4,2)+B4)
4.IF(DAY(B25)<25,MONTH(B25),MONTH(B25+9))
5.IF(COLUMN()=3,MONTH(--(B33&1)),LOOKUP(LEN(2^B33),ROW($1:$4),{"第一季度";"第二季度";"第三季度";"第四季度"}))