做对送花花啦--------时间函数（根据实际值求目标值）

zhaojiaoyang200

根据"实际值"求"目标值"，具体请见附件，谢谢!

CheryBTL

用IF函数搞定，有点长了，等下再减化：

1. =IF(B4="A",INT(C4)+3/4*(WEEKDAY(C4,2)<7)*(MOD(C4,1)<=3/4)+33/24*(WEEKDAY(C4,2)<6)*(MOD(C4,1)>3/4)+(WEEKDAY(C4,2)=6)*(MOD(C4,1)>3/4)*57/24+(WEEKDAY(C4,2)=7)*33/24,INT(C4)+33/24*(WEEKDAY(C4,2)<6)*(MOD(C4,1)<=3/4)+7/4*(WEEKDAY(C4,2)<6)*(MOD(C4,1)>3/4)+(WEEKDAY(C4)=7)*19/8-(WEEKDAY(C4)=1))

复制代码

感觉能用LOOKUP完成，今天抽时间再处理下吧。

mps777

1. =TEXT(C4,"e-m-d")+IF(OR(AND(B4="B",TEXT(C4,"aaa")="六"),TEXT(C4,"aaa")="日",AND(B4="A",AND(TEXT(C4,"aaa")="六",HOUR(C4)>=18))),LOOKUP(TEXT(C4,"aaa"),{"六",2;"日",1})+9/24,IF(AND(TEXT(C4,"aaa")<>{"日","六"},HOUR(C4)>=18),1,IF(AND(B4="B",AND(TEXT(C4,"aaa")<>{"六";"日"}),HOUR(C4)<18),15,9)/24)+LOOKUP(B4,{"A",9;"B",18})/24)

复制代码

想了许久啊……

WSSDD2011

=IF(AND(WEEKDAY(C4)<>1,(C4-INT(C4))*24<18),INT(C4)+18/24,"再IF就好了，不多写")

WSSDD2011

WEEKDAY()
INT()
AND()
OR()
IF()
运算符号
=IF(AND(WEEKDAY(C4)<>1,(C4-INT(C4))*24<18),INT(C4)+18/24,"再IF就好了，不多写")

wangg913

使用数值概念可简化大半。

wlc5201130

给个超长的
=IF(B4="A",IF(AND(VALUE(TEXT(C4,"h:mm:ss"))<3/4,WEEKDAY(C4,2)<7),INT(VALUE(C4))+3/4,IF(AND(VALUE(TEXT(C4,"h:mm:ss"))>3/4,WEEKDAY(C4,2)<6),INT(VALUE(C4))+1.375,WORKDAY(C4,1)+3/8)),IF(AND(VALUE(TEXT(C4,"h:mm:ss"))<3/4,WEEKDAY(C4,2)<6),INT(VALUE(C4))+1.375,IF(AND(VALUE(TEXT(C4,"h:mm:ss"))>3/4,WEEKDAY(C4,2)<6),INT(VALUE(C4))+1.75,WORKDAY(C4,1)+3/8)))

piny

D4

1. =IF(WEEKDAY(C4,2)<6,INT(C4)+3/4+CHOOSE(1+(MOD(C4,1)>3/4)+(B4="B"),0,5/8,1),INT(C4)+IF((MOD(C4,1)<3/4)*(B4="A")*(WEEKDAY(C4)=7),3/4,8-WEEKDAY(C4,2)+3/8))
   

复制代码

下拉

piny

再簡

1. =INT(C4)+3/4+CHOOSE(1+(WEEKDAY(C4,2)<6)*(1+(HOUR(C4)>18)+(B4="B"))+(HOUR(C4)<18)*(B4="A")*(WEEKDAY(C4)=7),8-WEEKDAY(C4,2)-3/8,,5/8,1)

复制代码

fatefree

F4

1. =IF(B4="A",IF(WEEKDAY(C4,2)+MOD(C4,1)<=6.75,IF(MOD(C4,1)<=0.75,INT(C4)+0.75,INT(C4)+1.375),FLOOR(C4,7)+2.375),IF(WEEKDAY(C4,2)<=5,IF(MOD(C4,1)<=0.75,INT(C4)+1.375,INT(C4)+1.75),FLOOR(C4,7)+2.375))

复制代码