求助！如何将字符串格式的时间转换为秒

Holiday2022

mykitami 发表于 2024-2-28 21:09
=SUM(IFERROR(MIDB(0&A2,FINDB({"天","小","分","秒"},0&A2)-2,2),0)*{86400,3600,60,1})

这个公式真的很巧妙，学习研究之后令我很难平静我认为这个公式有以下几个妙处：

1. 构建{"天","小","分","秒"}分隔符数组，使公式更加简洁
2. 用MIDB、FINDB，这是因为数字和文字的字节占位数量不同。不能用MID、FIND，因为数字和文字的字符占位数量是一样的。

字符和字节的区别：

简单来说，字符指类字形单位或符号，包括字母、数字、运算符号、标点符号和其他符号，以及一些功能性符号。而字节（Byte）是计算机信息技术用于计量存储容量的一种计量单位。

如果用字符数来计算数据长度的话，各种字母、数字、符号等等都是一个字，而如果用字节来计算数据长度的话，这里就有区别了，汉字、中文符号等等是两个字节，而数字、半角符号等等只能算一个字节。

在这个例子中，“1”就是1个字节、1个字符，而“天”就是2个字节、1个字符。

3. 利用FINDB取出字节所在位数时，为什么要用`0&A2`而不是`A2`？这可以联合下一点来理解。利用FINDB取出字节所在位数之后，为什么减2而不是其他数字？因为每个分隔符{"天","小","分","秒"}之前的数字最多为2位，任何时间都是这样的，不可能存在x天100小时100分钟100秒的时间。

回到要用`0&A2`而不是`A2`这一问题，加了前缀"0"之后，取两位才不会报错。

这也引申出本公式存在的一个问题，如果时间的天之前有3位数，结果就会有误，它的结果会忽略第1位数字。例如“222天6小时40分钟16秒”，它的结果与“22天6小时40分钟16秒”相同。

4. 根据拆分出来的数组，与1天、1小时、1分、1秒对应的秒数做乘积，然后进行累加，使得公式上很简短。

静听溪流

JXXFLJ 发表于 2024-2-29 09:10
请教大神：如果反过来再把秒转换为天时分秒，应该怎么写公式，183624秒转换为2天3小时24秒，如何用公式 ...

Holiday2022

1. =LET(a,QUOTIENT(H2,86400),b,QUOTIENT(MOD(H2,86400),3600),c,QUOTIENT(MOD(MOD(H2,86400),3600),60),d,MOD(MOD(MOD(H2,86400),3600),60),e,IF(a=0,"",a&"天"),f,IF(b=0,"",b&"小时"),g,IF(c=0,"",c&"分钟"),h,IF(d=0,"",d&"秒"),e&f&g&h)

复制代码

感谢6楼mykitami老师的公式！

另外，还是想跟楼主说，如果能够从源头（例如系统导出时）就把数据格式规整好，比如不漏掉时、分等信息（要"1天0小时54分钟23秒"，而不是"1天54分钟23秒"），结果将好弄很多！

JXXFLJ

静听溪流 发表于 2024-2-29 09:30

大神，为什么我用公式算出来是错的，显示1天3小时0分钟24秒
=TEXT(A1/24/3600,"d天h小时m分钟s秒")

JXXFLJ

为什么用完公式显示1天3小时0分钟24秒
TEXT(A1/24/3600,"d天h小时m分钟s秒")

Holiday2022

JXXFLJ 发表于 2024-2-29 10:27
为什么用完公式显示1天3小时0分钟24秒
TEXT(A1/24/3600,"d天h小时m分钟s秒")

看13楼的，你试了吗？

我要成为专家

1. =SUM(IFERROR(FILTER(FIND({"天";"时";"分";"秒";""},A2)^0*{86400;3600;60;1;0},ISNUMBER(FIND({"天";"时";"分";"秒";""},A2)))*TEXTSPLIT(A2,,{"天";"小时";"分钟";"分";"秒"}),))

复制代码

我要成为专家

1. =CONCAT(FILTER(MOD(INT(C2/{86400;3600;60;1}),{100;24;60;60})&{"天";"小时";"分钟";"秒"},MOD(INT(C2/{86400;3600;60;1}),{100;24;60;60})<>0))

复制代码

由秒变为原来，[c2]为秒所在单元格