文本日期取值换算的问题

slohart

原始数据都是xx小时xx分钟xx秒这样的，我把它换成小时，一般情况都没问题，但是奇葩的是偶尔会有xx分钟xx秒，xx小时xx秒这样的情况，输出值就变成0了……公司网络不允许上传东西，我就直接复制过来了。
公式是这样的：
=IFERROR(LEFT(A4,(FIND("小时",A4,1)-1))*1+((MID(A4,FIND("小时",A4,1)+2,FIND("分钟",A4,1)-FIND("小时",A4,1)-2)*1)*60+(MID(A4,FIND("分钟",A4,1)+2,FIND("秒",A4,1)-FIND("分钟",A4,1)-2))*1)/3600,0)
比如下面这组，左边是原始数据，右边是用这个公式做的，有两个0的都是应该有数据的，现在的解决办法是手动加上0小时51分钟1秒，或者2小时0分钟28秒。
求大佬帮忙修改一下。

1小时52分钟35秒	1.87639
0小时48分钟29秒	0.80806
16小时5分钟36秒	16.0933
2小时43分钟17秒	2.72139
2小时42分钟29秒	2.70806
51分钟1秒	0
1小时4分钟7秒	1.06861
2小时28秒	0

123-456789

不知能否达到你的要求？

爱沉浮

别问我怎么写出来的，我写出来后，已经不愿看第二遍了，太累眼了。建议楼主自定义个名称吧。


=(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(IF(IFERROR(FIND("秒",IF(IFERROR(FIND("分钟",IF(IFERROR(FIND("小时",A3),),SUBSTITUTE(A3,"小时",":"),"00:"&A3)),),SUBSTITUTE(IF(IFERROR(FIND("小时",A3),),SUBSTITUTE(A3,"小时",":"),"00:"&A3),"分钟",":"),IF(LEN(IF(IFERROR(FIND("小时",A3),),SUBSTITUTE(A3,"小时",":"),"00:"&A3))=3,IF(IFERROR(FIND("小时",A3),),SUBSTITUTE(A3,"小时",":"),"00:"&A3)&"00:",LEFT(IF(IFERROR(FIND("小时",A3),),SUBSTITUTE(A3,"小时",":"),"00:"&A3),FIND(":",IF(IFERROR(FIND("小时",A3),),SUBSTITUTE(A3,"小时",":"),"00:"&A3)))&"00:"&MID(IF(IFERROR(FIND("小时",A3),),SUBSTITUTE(A3,"小时",":"),"00:"&A3),FIND(":",IF(IFERROR(FIND("小时",A3),),SUBSTITUTE(A3,"小时",":"),"00:"&A3))+1,6)))),),SUBSTITUTE(IF(IFERROR(FIND("分钟",IF(IFERROR(FIND("小时",A3),),SUBSTITUTE(A3,"小时",":"),"00:"&A3)),),SUBSTITUTE(IF(IFERROR(FIND("小时",A3),),SUBSTITUTE(A3,"小时",":"),"00:"&A3),"分钟",":"),IF(LEN(IF(IFERROR(FIND("小时",A3),),SUBSTITUTE(A3,"小时",":"),"00:"&A3))=3,IF(IFERROR(FIND("小时",A3),),SUBSTITUTE(A3,"小时",":"),"00:"&A3)&"00:",LEFT(IF(IFERROR(FIND("小时",A3),),SUBSTITUTE(A3,"小时",":"),"00:"&A3),FIND(":",IF(IFERROR(FIND("小时",A3),),SUBSTITUTE(A3,"小时",":"),"00:"&A3)))&"00:"&MID(IF(IFERROR(FIND("小时",A3),),SUBSTITUTE(A3,"小时",":"),"00:"&A3),FIND(":",IF(IFERROR(FIND("小时",A3),),SUBSTITUTE(A3,"小时",":"),"00:"&A3))+1,6))),"秒",""),IF(IFERROR(FIND("分钟",IF(IFERROR(FIND("小时",A3),),SUBSTITUTE(A3,"小时",":"),"00:"&A3)),),SUBSTITUTE(IF(IFERROR(FIND("小时",A3),),SUBSTITUTE(A3,"小时",":"),"00:"&A3),"分钟",":"),IF(LEN(IF(IFERROR(FIND("小时",A3),),SUBSTITUTE(A3,"小时",":"),"00:"&A3))=3,IF(IFERROR(FIND("小时",A3),),SUBSTITUTE(A3,"小时",":"),"00:"&A3)&"00:",LEFT(IF(IFERROR(FIND("小时",A3),),SUBSTITUTE(A3,"小时",":"),"00:"&A3),FIND(":",IF(IFERROR(FIND("小时",A3),),SUBSTITUTE(A3,"小时",":"),"00:"&A3)))&"00:"&MID(IF(IFERROR(FIND("小时",A3),),SUBSTITUTE(A3,"小时",":"),"00:"&A3),FIND(":",IF(IFERROR(FIND("小时",A3),),SUBSTITUTE(A3,"小时",":"),"00:"&A3))+1,6)))&"00")*1,"h小时m分钟s秒"),"小时",":"),"分钟",":"),"秒",""))*86400/3600

slohart

123-456789 发表于 2017-11-22 21:48
不知能否达到你的要求？

666666，大神受小弟一拜

slohart

爱沉浮 发表于 2017-11-23 10:55
别问我怎么写出来的，我写出来后，已经不愿看第二遍了，太累眼了。建议楼主自定义个名称吧。

666666，大神受小弟一拜

丢丢表格

走宏表

1. 时间=EVALUATE(0&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!A2,"小时","*1+0"),"分钟","/60+"),"秒","/3600"))

复制代码