[已解决][求助]日期相差天数问题.....在线等

jjchan2005

A1 输入 3天20小时
A2 输入 =NOW()
A3 输入:
=TEXT(NOW()+VALUE(LEFT(A1,FIND("天",A1,1)-1))+VALUE(MID(A1,FIND("天",A1,1)+1,FIND("小",A1,1)-FIND("天",A1,1)-1))/24,"yyyy-mm-dd hh:mm")

但如果A1带上分钟的话，这个公式不能计算。麻烦谁能帮忙修改下。然A3的公式在A3带上分钟的情况下依然可以计算

A3 便是结果

最佳答案

月排行榜 / 总排行榜

lotte

2010-9-1 23:17

=TEXT(NOW()+LEFT(A1,FIND("天",A1,1)-1)+SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("天",A1,1)+1,99),"小时",":"),"分",""),"yyyy-mm-dd hh:mm")

跳转到最佳答案楼层

QQ9947

A1内会有哪几种情况的数据？

尽量把可能出现的情况多列举一点才能更完美的解决问题。

那么的帅

A3 =TEXT(NOW()+VALUE(LEFT(A1,FIND("天",A1,1)-1))+VALUE(MID(A1,FIND("天",A1,1)+1,FIND("小",A1,1)-FIND("天",A1,1)-1))/24+VALUE(MID(A1,FIND("小",A1,1)+2,FIND("分",A1,1)-FIND("小",A1,1)-2))/24/60,"yyyy-mm-dd hh:mm")

lotte

=TEXT(NOW()+LEFT(A1,FIND("天",A1,1)-1)+SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("天",A1,1)+1,99),"小时",":"),"分",""),"yyyy-mm-dd hh:mm")

QQ9947

=TEXT(NOW()+SUM((SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"天","      "),"小时","      "),"分","      "),{1,6,12},5)," ",)&"00")%/{1,24,1440}),"yyyy-mm-dd hh:mm")

bbwsj

 

[em01]

[此贴子已经被作者于2010-9-1 23:40:54编辑过]