如何在Excel中将多种格式时间统一为hh:mm:ss格式？

针对你提到的三种时间数据格式，这里提供两种Excel公式方案，分别适配新版和旧版Excel，实现自动统一格式为hh:mm:ss。

方案一：适用于Excel 365/2021及以后版本（推荐） #

利用LET和TEXTSPLIT函数简化逻辑，代码更易读维护：

=LET(
    txt, TEXT(A1,"@"),
    colons, LEN(txt)-LEN(SUBSTITUTE(txt,":","")),
    parts, TEXTSPLIT(txt,":"),
    IF(colons=2,
        TEXT(VALUE(parts[1])/24 + VALUE(parts[2])/(24*60) + VALUE(parts[3])/(24*60*60),"hh:mm:ss"),
        IF(LEFT(txt,1)=":",
            TEXT(VALUE(RIGHT(txt,2))/(24*60*60),"hh:mm:ss"),
            IF(colons=1,
                TEXT(VALUE(parts[1])/(24*60) + VALUE(parts[2])/(24*60*60),"hh:mm:ss"),
                ""
            )
        )
    )
)

逻辑说明： #

1. txt：将单元格内容统一转为文本格式，消除数值型时间和文本型时间的差异。
2. colons：统计文本中冒号的数量，判断时间格式类型。
3. 分情况处理：
   • 冒号数量为2（如09:00:00）：直接将时、分、秒转换为标准时间值并格式化。
   • 文本以冒号开头（如:00）：提取右侧秒数，转换为00:00:xx格式。
   • 冒号数量为1（如06:34，包括Excel误识别为hh:mm的情况）：将左侧视为分钟、右侧视为秒，转换为00:mm:ss格式。

方案二：适用于旧版Excel（无LET/TEXTSPLIT函数） #

用传统函数实现相同逻辑：

=IF(LEN(TEXT(A1,"@"))-LEN(SUBSTITUTE(TEXT(A1,"@"),":",""))=2,
    TEXT(A1,"hh:mm:ss"),
    IF(LEFT(TEXT(A1,"@"),1)=":",
        TEXT(RIGHT(TEXT(A1,"@"),2)/(24*60*60),"hh:mm:ss"),
        TEXT((LEFT(TEXT(A1,"@"),FIND(":",TEXT(A1,"@"))-1)/(24*60)+RIGHT(TEXT(A1,"@"),LEN(TEXT(A1,"@"))-FIND(":",TEXT(A1,"@")))/(24*60*60)),"hh:mm:ss")
    )
)

使用方法： #

1. 假设原始数据在A列，在B1单元格输入对应公式。
2. 选中B1单元格，下拉填充至所有需要处理的行。
3. 后续粘贴新数据到A列后，B列公式会自动更新结果。

内容的提问来源于stack exchange，提问作者Sam Thompson