在Excel中将大时间格式转换为十进制 [英] Converting large time format to decimal in excel

问题描述

我正在尝试将excel中的较大时间值转换为小时的十进制数.

I'm trying to convert a large time value in excel to a decimal number for hours.

我目前有一列为呼叫中心加起来的"Ready time"(准备时间)，总计为3545:20:02.我现在希望以十进制格式向我显示相同的小时数，例如3545.333，因为它用于另一次计算.

I currently have a column adding up "Ready time" for a call centre which is 3545:20:02 as a SUM. I now want that to show me the same hours in a decimal format e.g. 3545.333 as it's used in another calculation.

作为参考，当我将上述时间转换为一般excel值时，它是147.7222454.

For reference, when I convert the above time to a General excel value, it is 147.7222454.

我一直在使用的公式是:= IFERROR((DAY(M54)* 24)+ HOUR(M54)+(MINUTE(M54)/60)，0)，对于较小的时间值，它可以正常工作.

The formula I've been using is: =IFERROR((DAY(M54)*24) + HOUR(M54) + (MINUTE(M54)/60),0) and has been working fine for smaller time values.

提前谢谢！

推荐答案

Excel以天数(1天= 1)进行计数，因此对于小时，您只需乘以24，即

Excel counts in days (1 day = 1) so for hours you just multiply by 24, i.e.

=M54*24

将结果单元格设置为带有必需的小数位数的数字

format result cell as number with required number of decimals

[您当前公式失败的原因是由于DAY函数-DAY是月份中的某天，所以当时间值> = 32 * 24 = 768小时时，它对于您而言将失败]

[the reason your current formula fails is because of DAY function - DAY is day of the month so it fails for you when the time value is >= 32*24 = 768 hours]

这篇关于在Excel中将大时间格式转换为十进制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！