☰

将 d hr ms 转换为 [h]:mm

tag-icon • tag-icon microsoft-excel format
将 d hr ms 转换为 [h]:mm

我有格式为“d hr m s”的数据，想将其更改为 [h]:mm。其中 h 是 d*24+hr。

例如这是我当前拥有的数据：

在此处输入图片描述

我该怎么做才能格式化单元格，或者我可以使用什么公式将单元格数据更改为我想要的格式？

干杯

答案1

创建 Excel 可以识别的时间格式并添加天数

=IFERROR(LEFT(A1,FIND("d",A1)-1),0)+(IF(ISNUMBER(FIND("hr",A1)),"","00:")&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,IFERROR(FIND("d",A1),-1)+2,LEN(A1)),"hr ",":"),"m ",":"),"s",""))

在此处输入图片描述

答案2

您可以使用免费的 Excel 有效地解决这个问题RegEx 查找/替换加入。

（我与该工具没有任何关系。）

只需输入以下公式并将结果格式化为[h]:mm

=SUMPRODUCT(RegExFind(A2,"(\d+)"),{86400,3600,60,1})/86400

在此处输入图片描述

答案3

结合几个基本的 Excel 函数将ISNUMBER数据转换为所需的格式。

在此处输入图片描述

  • 单元格中的公式I31，填写完毕。 =IF(ISNUMBER(FIND("d",H31)),LEFT(H31,FIND("d",H31)-1)*24)+IF(ISNUMBER(FIND("h",H31)),MID(0&H31,MAX(1,FIND("h",0&H31)-2),2))+MID(0&H31,MAX(1,FIND("m",0&H31)-2),2)/60

  • 应用于。Appropriate Cell format with 2 Decimals​I31:I35

編輯：

因为我错误地跳过了 OP 的需求Results in [H]:mm Time format，所以我发布了这个新公式，返回时间是期望格式。

请读者忽略上面显示的公式。

在此处输入图片描述

  • 单元格中的公式I46，填写完毕。

    =SUM(MID(0&H46&"000",FIND({"d","h","m"},0&H46&"xxdhm")-2,2)*{1440,60,1})/86400*60

注意：应用[h]:mm时间格式I46:I53

  • 单元格中的公式J46，填写完毕。

    =TEXT(SUM(MID(0&H46&"000",FIND({"d","h","m"},0&H46&"xxdhm")-2,2)*{1440,60,1})/86400*60,"[h]:mm")

根据需要调整公式中的单元格引用。

相关内容