【66期】时间计算题----《时间之差》[已汇总]

alzeng

C2数组公式（连等号142字符）：
=SUBSTITUTE(TEXT(SUM(SUBSTITUTE(SUBSTITUTE(IF(ISERR(FIND("天",A2:B2)),"0 ","")&A2:B2,"天"," "),"小时","/24")*{1,-1}),"[>0]#天#/24天;;"),"/24天","小时")

wddn

C2=SUBSTITUTE(SUBSTITUTE(TEXT(SUM(SUBSTITUTE(IF(ISERR(FIND("天",A2:B2)),"0 ",)&SUBSTITUTE(A2:B2,"天"," "),"小时","/24")*{1,-1}),"[>=1]0天?/24;[>]H小时;"),"/24","小时"),"     ","天")

Yangqianqian

我是新到EXCEK HOME的，才看到本题，虽然过了结题时间，但觉得题目有意思，所以参与了，楼主不必考虑分数。

[ 本帖最后由 Yangqianqian 于 2010-10-1 20:48 编辑 ]

fugb-2010

时间已过4天，为何还未进行评分？如果是国庆节放假原因，为何不提前准备评分？

zhangjimfu

=TEXT(INT(SUM((IF(ISERR(LEFT(A2:B2,FIND("天",A2:B2)-1)*{24,-24}),LEFT(A2:B2,LEN(A2:B2)-2),LEFT(A2:B2,FIND("天",A2:B2)-1)*{24,24})+IF(ISERR(MID(A2:B2,FIND("天",A2:B2)+1,LEN(A2:B2)-FIND("天",A2:B2)-2)),0,MID(A2:B2,FIND("天",A2:B2)+1,LEN(A2:B2)-FIND("天",A2:B2)-2)))*{1,-1})/24),"[>0]#天;")&TEXT(MOD(--(0&TEXT(SUM((IF(ISERR(LEFT(A2:B2,FIND("天",A2:B2)-1)*{24,-24}),LEFT(A2:B2,LEN(A2:B2)-2),LEFT(A2:B2,FIND("天",A2:B2)-1)*{24,24})+IF(ISERR(MID(A2:B2,FIND("天",A2:B2)+1,LEN(A2:B2)-FIND("天",A2:B2)-2)),0,MID(A2:B2,FIND("天",A2:B2)+1,LEN(A2:B2)-FIND("天",A2:B2)-2)))*{1,-1}),"[>0];")),24),"[>0]#小时;")

来的太晚了啊!收获不小

zhangjimfu

原帖由 一剑惊心 于 2010-9-24 18:51 发表
参与的人不多啊
给个我的公式
=SUBSTITUTE(TEXT(SUM(SUBSTITUTE(TEXT(SUBSTITUTE(A2:B2,"小时",),"0 00")&"/24","天"," 0")*{1,-1}),"#天#小时/24天;;"),"/24天",)

经典中的经典!

A羽飞A

原帖由 一剑惊心 于 2010-9-24 18:51 发表
参与的人不多啊
给个我的公式
=SUBSTITUTE(TEXT(SUM(SUBSTITUTE(TEXT(SUBSTITUTE(A2:B2,"小时",),"0 00")&"/24","天"," 0")*{1,-1}),"#天#小时/24天;;"),"/24天",)

厉害！

学习了！

wangg913

少于120！！
leeyong和一剑的思路确实＂惊心＂。
精彩。

Zaezhong

上面解法的确与众不同

piny

对于Text的格式转换 果然仍有很长的路需学习

~~话说先前也曾试图参与 写了个巨长的公式 后来一剑老师改以180为参赛标准 就删了答案了~~

[ 本帖最后由 piny 于 2010-10-5 22:19 编辑 ]