接力赛：时之差

一剑惊心

<p><strong><font color="#ff0000">要求：</font></strong></p><p>时间2减去时间1，最终结果格式为：<u><font color="#0000ff">N天N小时</font></u>，但不允许出现<u><font color="#cc00ff">0天</font></u>或<font color="#cc00ff"><u>0小时</u></font>这种无效格式</p><p>时间1和时间2为文本格式，而非自定义格式</p><p>不准使用VBA</p><p>不准定义名称</p><p>不准使用加载宏函数</p><p>不准使用辅助区域</p><p>公式函数请以office2003版本为准</p><p><strong><font color="#ff0000">说明：</font></strong></p><p>时间2不会大于时间1</p><p>最大时间为999天23小时，最小时间为1小时，天数和小时数没有小数</p><p><font color="#ff0000"><strong>奖励：</strong></font></p><p>基数BB为10，公式长度基数为300</p><p>BB=<u><font color="#0000ff">10*1.2^接力棒数+30*300/公式长度</font></u>（如第5棒公式长度210，则BB=10*1.2^5+30*300/210=68BB）</p><p><br/>

[此贴子已经被作者于2010-8-23 21:42:58编辑过]

dengyf

<script type="text/javascript" language="javascript">var ispost=0;        function ctlent()        {                        parent.copy_preview();                 if(event.ctrlKey &amp;&amp; window.event.keyCode==13&amp;&amp;ispost==0)                {                        ispost=1;                        parent.Dvbbs_CopyData("Body");                         parent.document.Dvform.Submit.onclick();                        parent.document.Dvform.submit();                }        }function imgresize(o){if(o.width &gt; 500 ){        o.style.width='500px';}if(o.height &gt; 800){        o.style.height='800px';}}</script><style type="text/css">.quote{margin:5px 20px;border:1px solid #CCCCCC;padding:5px; background:#F3F3F3 }
body{boder:0px}.HtmlCode{margin:5px 20px;border:1px solid #CCCCCC;padding:5px;background:#FDFDDF;font-size:14px;font-family:Tahoma;font-style : oblique;line-height : normal ;font-weight:bold;}
body{boder:0px}</style><p>没人接手哦，给个超长公式吧，好让大家精减（未作出任何精减）</p>
<p>=TEXT(INT(SUM({1,-1}*TEXT(LEFT(A2:B2,FIND("天",A2:B2&amp;"天")-1),"0;0;;!0"),{1,-1}*TEXT(RIGHT(SUBSTITUTE(SUBSTITUTE(A2:B2,"小时",""),"天","天0"),2),"0;0;;!0")/24)),"0天;;;")&amp;TEXT("0"&amp;MID(SUM({1,-1}*({1,0}+TEXT(RIGHT(SUBSTITUTE(SUBSTITUTE(A2:B2,"小时",""),"天","天0"),2),"0;0;;!0")/24)),2,99),"H小时;;;")</p>
<p>287字符</p>

dengyf

=TEXT(INT(SUM({1,-1}*TEXT(LEFT(A2:B2,FIND("天",A2:B2&amp;"天")-1),"0;0;;!0"),{1,-1}*TEXT(RIGHT(SUBSTITUTE(SUBSTITUTE(A2:B2,"小时",""),"天","天0"),2),"0;0;;!0")/24)),"0天;;;")&amp;TEXT("0"&amp;MID(SUM({1,-1}*({1,0}+TEXT(RIGHT(SUBSTITUTE(SUBSTITUTE(A2:B2,"小时",""),"天","天0"),2),"0;0;;!0")/24)),2,9),"H小时;;;")<br/>减一个字符286算不算？<br/>[em04]

dengyf

还没人接？刷分啊<br/>=TEXT(INT(SUM({1,-1}*TEXT(LEFT(A2:B2,FIND("天",A2:B2&amp;"天")-1),"0;0;;!0"),{1,-1}*TEXT(RIGHT(SUBSTITUTE(SUBSTITUTE(A2:B2,"小时",""),"天","天0"),2),"0;0;;!0")/24)),"0天;;;")&amp;TEXT(0&amp;MID(SUM({1,-1}*({1,0}+TEXT(RIGHT(SUBSTITUTE(SUBSTITUTE(A2:B2,"小时",""),"天","天0"),2),"0;0;;!0")/24)),2,9),"H小时;;;")<br/>284字符。一点点减，哈哈。<br/>

dengyf

=TEXT(INT(SUM({1,-1}*TEXT(LEFT(A2:B2,FIND("天",A2:B2&amp;"天")-1),"0;0;;!0"),{1,-1}*TEXT(RIGHT(SUBSTITUTE(SUBSTITUTE(A2:B2,"小时",),"天","天0"),2),"0;0;;!0")/24)),"0天;;;")&amp;TEXT(0&amp;MID(SUM({1,-1}*({1,0}+TEXT(RIGHT(SUBSTITUTE(SUBSTITUTE(A2:B2,"小时",),"天","天0"),2),"0;0;;!0")/24)),2,9),"H小时;;;")<br/>280字符<br/>

chunlin1wang

<p>不算等号214字符</p><p>=SUBSTITUTE(SUBSTITUTE(TEXT(SUM((IF(ISERR(FIND("天",A2:B2)),0,LEFT(A2:B2,FIND("天",A2:B2)-1))*24+(0&amp;RIGHT(SUBSTITUTE(SUBSTITUTE(A2:B2,"小时",""),"天","00"),2)))*{1,-1})/24,"[&gt;=1]0天?/24;[&gt;0]?/24;"),"/24","小时"),"&nbsp;&nbsp;&nbsp; ","天")</p>

chunlin1wang

<p>不算等号204字符</p><p>=SUBSTITUTE(SUBSTITUTE(TEXT(SUM((TEXT(LEFT(A2:B2,FIND("天",A2:B2&amp;"天")-1),"0;;;!0")*24+(0&amp;RIGHT(SUBSTITUTE(SUBSTITUTE(A2:B2,"小时",""),"天","00"),2)))*{1,-1})/24,"[&gt;=1]0天?/24;[&gt;0]?/24;"),"/24","小时"),"&nbsp;&nbsp;&nbsp; ","天")</p>

飞雨飘

呵呵再少一个字符不算等号<br/>=SUBSTITUTE(SUBSTITUTE(TEXT(SUM({1,-1}*(TEXT(LEFT(A2:B2,FIND("天",A2:B2&amp;"天")-1),"0;0;;!0")+TEXT(RIGHT(SUBSTITUTE(SUBSTITUTE(A2:B2,"小时",),"天","天0"),2),"0;0;;!0")/24)),"[&gt;=1]0天?/24;[&gt;0]?/24;"),"/24","小时"),"&#160;&#160;&#160;&#160; ","天")<br/>213字符<br/>

飞雨飘

汗，上面一贴没法改哦<br/>=SUBSTITUTE(SUBSTITUTE(TEXT(SUM((TEXT(LEFT(A2:B2,FIND("天",A2:B2&amp;"天")-1),"0;;;!0")*24+(0&amp;RIGHT(SUBSTITUTE(SUBSTITUTE(A2:B2,"小时",),"天","00"),2)))*{1,-1})/24,"[&gt;=1]0天?/24;[&gt;0]?/24;"),"/24","小时"),"&#160;&#160;&#160; ","天")<br/>202字符<br/>

dengyf

SUBSTITUTE(SUBSTITUTE(TEXT(SUM((TEXT(LEFT(A2:B2,FIND("天",A2:B2&amp;"天")-1),"0;;;!0")*24+RIGHT(SUBSTITUTE(SUBSTITUTE(A2:B2,"小时",),"天","00"),2))*{1,-1})/24,"[&gt;=1]0天?/24;[&gt;0]?/24;"),"/24","小时"),"&#160;&#160;&#160; ","天")<br/>198个字符<br/>