...时间,格式是 20110904 204331 。怎么计算两个时间间隔
发布网友
共2个回答
热心网友
如图：
B3公式为：
=IF(TIME(MID(A3,LEN(A3)-5,2),MID(A3,LEN(A3)-3,2),RIGHT(A3,2))>=TIME(MID(A2,LEN(A2)-5,2),MID(A2,LEN(A2)-3,2),RIGHT(A2,2)),DATEDIF(DATE(LEFT(A2,4),MID(A2,5,2),MID(A2,7,2)),DATE(LEFT(A3,4),MID(A3,5,2),MID(A3,7,2)),"d")&"天"&HOUR(TIME(MID(A3,LEN(A3)-5,2),MID(A3,LEN(A3)-3,2),RIGHT(A3,2))-TIME(MID(A2,LEN(A2)-5,2),MID(A2,LEN(A2)-3,2),RIGHT(A2,2)))&"小时"&MINUTE(TIME(MID(A3,LEN(A3)-5,2),MID(A3,LEN(A3)-3,2),RIGHT(A3,2))-TIME(MID(A2,LEN(A2)-5,2),MID(A2,LEN(A2)-3,2),RIGHT(A2,2)))&"分钟"&SECOND(TIME(MID(A3,LEN(A3)-5,2),MID(A3,LEN(A3)-3,2),RIGHT(A3,2))-TIME(MID(A2,LEN(A2)-5,2),MID(A2,LEN(A2)-3,2),RIGHT(A2,2)))&"秒",DATEDIF(DATE(LEFT(A2,4),MID(A2,5,2),MID(A2,7,2)),DATE(LEFT(A3,4),MID(A3,5,2),MID(A3,7,2)),"d")-1&"天"&HOUR(TIME(23,59,59)+TIME(0,0,1)+TIME(MID(A3,LEN(A3)-5,2),MID(A3,LEN(A3)-3,2),RIGHT(A3,2))-TIME(MID(A2,LEN(A2)-5,2),MID(A2,LEN(A2)-3,2),RIGHT(A2,2)))&"小时"&MINUTE(TIME(23,59,59)+TIME(0,0,1)+TIME(MID(A3,LEN(A3)-5,2),MID(A3,LEN(A3)-3,2),RIGHT(A3,2))-TIME(MID(A2,LEN(A2)-5,2),MID(A2,LEN(A2)-3,2),RIGHT(A2,2)))&"分钟"&SECOND(TIME(23,59,59)+TIME(0,0,1)+TIME(MID(A3,LEN(A3)-5,2),MID(A3,LEN(A3)-3,2),RIGHT(A3,2))-TIME(MID(A2,LEN(A2)-5,2),MID(A2,LEN(A2)-3,2),RIGHT(A2,2)))&"秒")
C13公式为：
=IF(TIME(LEFT(B13,2),MID(B13,3,2),RIGHT(B13,2))>=TIME(LEFT(B12,2),MID(B12,3,2),RIGHT(B12,2)),DATEDIF(DATE(LEFT(A12,4),MID(A12,5,2),RIGHT(A12,2)),DATE(LEFT(A13,4),MID(A13,5,2),RIGHT(A13,2)),"d")&"天"&HOUR(TIME(LEFT(B13,2),MID(B13,3,2),RIGHT(B13,2))-TIME(LEFT(B12,2),MID(B12,3,2),RIGHT(B12,2)))&"小时"&MINUTE(TIME(LEFT(B13,2),MID(B13,3,2),RIGHT(B13,2))-TIME(LEFT(B12,2),MID(B12,3,2),RIGHT(B12,2)))&"分钟"&SECOND(TIME(LEFT(B13,2),MID(B13,3,2),RIGHT(B13,2))-TIME(LEFT(B12,2),MID(B12,3,2),RIGHT(B12,2)))&"秒",DATEDIF(DATE(LEFT(A12,4),MID(A12,5,2),RIGHT(A12,2)),DATE(LEFT(A13,4),MID(A13,5,2),RIGHT(A13,2)),"d")-1&"天"&HOUR(TIME(23,59,59)+TIME(0,0,1)-TIME(LEFT(B12,2),MID(B12,3,2),RIGHT(B12,2))+TIME(LEFT(B13,2),MID(B13,3,2),RIGHT(B13,2)))&"小时"&MINUTE(TIME(23,59,59)+TIME(0,0,1)-TIME(LEFT(B12,2),MID(B12,3,2),RIGHT(B12,2))+TIME(LEFT(B13,2),MID(B13,3,2),RIGHT(B13,2)))&"分钟"&SECOND(TIME(23,59,59)+TIME(0,0,1)-TIME(LEFT(B12,2),MID(B12,3,2),RIGHT(B12,2))+TIME(LEFT(B13,2),MID(B13,3,2),RIGHT(B13,2)))&"秒")
热心网友
两个以天为单位的数据计算是不可能得到小时 / 分钟 / 秒的
热心网友
如图：
B3公式为：
=IF(TIME(MID(A3,LEN(A3)-5,2),MID(A3,LEN(A3)-3,2),RIGHT(A3,2))>=TIME(MID(A2,LEN(A2)-5,2),MID(A2,LEN(A2)-3,2),RIGHT(A2,2)),DATEDIF(DATE(LEFT(A2,4),MID(A2,5,2),MID(A2,7,2)),DATE(LEFT(A3,4),MID(A3,5,2),MID(A3,7,2)),"d")&"天"&HOUR(TIME(MID(A3,LEN(A3)-5,2),MID(A3,LEN(A3)-3,2),RIGHT(A3,2))-TIME(MID(A2,LEN(A2)-5,2),MID(A2,LEN(A2)-3,2),RIGHT(A2,2)))&"小时"&MINUTE(TIME(MID(A3,LEN(A3)-5,2),MID(A3,LEN(A3)-3,2),RIGHT(A3,2))-TIME(MID(A2,LEN(A2)-5,2),MID(A2,LEN(A2)-3,2),RIGHT(A2,2)))&"分钟"&SECOND(TIME(MID(A3,LEN(A3)-5,2),MID(A3,LEN(A3)-3,2),RIGHT(A3,2))-TIME(MID(A2,LEN(A2)-5,2),MID(A2,LEN(A2)-3,2),RIGHT(A2,2)))&"秒",DATEDIF(DATE(LEFT(A2,4),MID(A2,5,2),MID(A2,7,2)),DATE(LEFT(A3,4),MID(A3,5,2),MID(A3,7,2)),"d")-1&"天"&HOUR(TIME(23,59,59)+TIME(0,0,1)+TIME(MID(A3,LEN(A3)-5,2),MID(A3,LEN(A3)-3,2),RIGHT(A3,2))-TIME(MID(A2,LEN(A2)-5,2),MID(A2,LEN(A2)-3,2),RIGHT(A2,2)))&"小时"&MINUTE(TIME(23,59,59)+TIME(0,0,1)+TIME(MID(A3,LEN(A3)-5,2),MID(A3,LEN(A3)-3,2),RIGHT(A3,2))-TIME(MID(A2,LEN(A2)-5,2),MID(A2,LEN(A2)-3,2),RIGHT(A2,2)))&"分钟"&SECOND(TIME(23,59,59)+TIME(0,0,1)+TIME(MID(A3,LEN(A3)-5,2),MID(A3,LEN(A3)-3,2),RIGHT(A3,2))-TIME(MID(A2,LEN(A2)-5,2),MID(A2,LEN(A2)-3,2),RIGHT(A2,2)))&"秒")
C13公式为：
=IF(TIME(LEFT(B13,2),MID(B13,3,2),RIGHT(B13,2))>=TIME(LEFT(B12,2),MID(B12,3,2),RIGHT(B12,2)),DATEDIF(DATE(LEFT(A12,4),MID(A12,5,2),RIGHT(A12,2)),DATE(LEFT(A13,4),MID(A13,5,2),RIGHT(A13,2)),"d")&"天"&HOUR(TIME(LEFT(B13,2),MID(B13,3,2),RIGHT(B13,2))-TIME(LEFT(B12,2),MID(B12,3,2),RIGHT(B12,2)))&"小时"&MINUTE(TIME(LEFT(B13,2),MID(B13,3,2),RIGHT(B13,2))-TIME(LEFT(B12,2),MID(B12,3,2),RIGHT(B12,2)))&"分钟"&SECOND(TIME(LEFT(B13,2),MID(B13,3,2),RIGHT(B13,2))-TIME(LEFT(B12,2),MID(B12,3,2),RIGHT(B12,2)))&"秒",DATEDIF(DATE(LEFT(A12,4),MID(A12,5,2),RIGHT(A12,2)),DATE(LEFT(A13,4),MID(A13,5,2),RIGHT(A13,2)),"d")-1&"天"&HOUR(TIME(23,59,59)+TIME(0,0,1)-TIME(LEFT(B12,2),MID(B12,3,2),RIGHT(B12,2))+TIME(LEFT(B13,2),MID(B13,3,2),RIGHT(B13,2)))&"小时"&MINUTE(TIME(23,59,59)+TIME(0,0,1)-TIME(LEFT(B12,2),MID(B12,3,2),RIGHT(B12,2))+TIME(LEFT(B13,2),MID(B13,3,2),RIGHT(B13,2)))&"分钟"&SECOND(TIME(23,59,59)+TIME(0,0,1)-TIME(LEFT(B12,2),MID(B12,3,2),RIGHT(B12,2))+TIME(LEFT(B13,2),MID(B13,3,2),RIGHT(B13,2)))&"秒")
热心网友
两个以天为单位的数据计算是不可能得到小时 / 分钟 / 秒的
