我希望在 Excel 2010 中格式化日期时间以显示如下内容：
1.234 ==> 1 天，5 小时，36 分钟
我知道如何在更简单的场景中做类似的事情
对于仅限几天的情况，我可以使用条件格式，如下所示：
[=1] d" day";d" days"
但这不适用于时间戳。
对于整个集合，我可以这样做：
d" days, "h" hours, "m" minutes"
但是当其中一个值（通常是天）是时，这看起来很有趣1。
我不确定如何融合这两个概念。如果有
1 天，5 分钟
如果小时值为 0，但
1 天, 0 小时, 5 分钟
效果一样好。
我可以使用 VBA/UDF 来快速找到解决方案，但目前工作簿上没有其他代码，因此如果可能的话，我想避免使用这种方法。我还知道我可以在另一个单元格中解析出该值，如下所示：
=DAY(A1) & IF(DAY(A1)=1," day"," days") & ", " & HOUR(A1) & IF(HOUR(A1)=1," hour"," hours") & ", " & etc...
但这相当笨重，需要在一组新的单元格中运行计算，而不是仅在当前数据列中插入格式即可完成。
使用条件数字格式实际上是否可行，还是我只能使用更冗长、更冗长的方法？
答案1
你不能用风俗数字格式，对于像 [=1] 这样的简单条件来说它太复杂了，并且您不能在类型框中使用任何函数。
您需要在单独的单元格中使用公式。（我想您可以隐藏原始列。）
答案2
根据这里的反馈，这似乎对于简单的格式来说太多了。相反，我继续制作了一些公式来创建它。
要显示所有值，包括所有 0 值：
=DAY(A1) & IF(DAY(A1)=1," day"," days") & ", " & HOUR(A1) & IF(HOUR(A1)=1," hour"," hours") & ", " & MINUTE(A1) & IF(MINUTE(A1)=1," minute"," minutes") & ", " & SECOND(A1) & IF(SECOND(A1)=1," second"," seconds")
1.25001 : 1 day, 6 hours, 0 minutes, 1 second
2.25001 : 2 days, 6 hours, 0 minutes, 1 second
1.04167 : 1 day, 1 hour, 0 minutes, 0 seconds
仅删除尾随的 0 值：
=DAY(A1) & IF(DAY(A1)=1," day"," days") & IF(HOUR(A1) + MINUTE(A1) + SECOND(A1)>0, ", " & HOUR(A1) & IF(HOUR(A1)=1," hour"," hours") & IF( MINUTE(A1) + SECOND(A1)>0, ", " & MINUTE(A1) & IF(MINUTE(A1)=1," minute"," minutes") & IF( SECOND(A1)>0, ", " & SECOND(A1) & IF(SECOND(A1)=1," second"," seconds"), ""), ""), "")
1.25001 : 1 day, 6 hours, 0 minutes, 1 second
2.25001 : 2 days, 6 hours, 0 minutes, 1 second
1.04167 : 1 day, 1 hour
如果应省略所有 0 值，则可行如下方法：
=DAY(A1) & IF(DAY(A1)=1," day"," days") & IF(HOUR(A1) + MINUTE(A1) + SECOND(A1)>0, ", ", "") & IF(HOUR(A1)>0,HOUR(A1) & IF(HOUR(A1)=1," hour"," hours") & IF( MINUTE(A1) + SECOND(A1)>0, ", ", ""),"") & IF(MINUTE(A1)>0, MINUTE(A1) & IF(MINUTE(A1)=1," minute"," minutes") & IF( SECOND(A1)>0, ", ", ""),"") & IF(SECOND(A1)>0,SECOND(A1) & IF(SECOND(A1)=1," second"," seconds"),"")
1.25001 : 1 day, 6 hours, 1 second
2.25001 : 2 days, 6 hours, 1 second
1.04167 : 1 day, 1 hour