如何在Excel中计算日期时间差并求取平均审核时长？

问题根源 #

你之前用TEXT函数生成的是文本格式的时长，Excel的AVERAGE函数无法对文本值计算平均值，因此需要直接基于转换后的日期时间数值计算平均差值，再格式化结果。

具体操作步骤 #

1. 保留日期时间数值列
   确保已通过=DATEVALUE(MID(H2,1,10))+TIMEVALUE(MID(H2,12,8))（创建日期）和对应公式转换审核日期，得到Excel可识别的日期时间数值（如25/10/2023 09:55格式，本质是数值类型）。

2. 计算单条有效审核时长
   在空白列（如O列）输入公式，自动跳过null记录：

   =IF(N2="null","",N2-M2)
   

   其中N2为审核日期转换后的单元格，M2为创建日期转换后的单元格，结果是以天为单位的数值（例如3.019444代表3天28分钟）。

3. 计算平均时长数值
   在空白单元格输入公式，自动忽略空值和无效记录：

   =AVERAGEIF(O:O,"<>""""")
   

4. 格式化平均时长为目标样式
   将平均数值转换成指定格式，输入公式：

   =TEXT(P2,"dd")&" 天 "&TEXT(P2,"hh:mm")&" 分钟"
   

   其中P2为存放平均数值的单元格，最终会得到「平均审核时长：18 天 00:28 分钟」的结果。

一步到位简化公式（适用于Excel 365/2021） #

无需分步计算，直接输入以下公式即可得到格式化后的平均时长：

=TEXT(AVERAGEIF(N:N,"<>null",N:N-M:M),"dd")&" 天 "&TEXT(AVERAGEIF(N:N,"<>null",N:N-M:M),"hh:mm")&" 分钟"

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