将分钟值格式化为“N 小时,M 分钟”字符串的公式

将分钟值格式化为“N 小时,M 分钟”字符串的公式

我指的是这个问题分别編輯回答。有一列以分钟为单位的时间段。三小时零两分钟的时间段将是182。如何使用公式有条件地格式化单元格以获得以下结果?

00 minutes

11 minute

22 minutes

601 hour

1202 hours

1813 hours, 1 minute

631 hour, 3 minutes

我是否需要进行一些(四舍五入)除法和模数计算?或者是否有用于此类操作的内置函数?

答案1

=IF(A1=0,"0 minutes",IF(A1>=60,FLOOR(A1/60,1) & " hour" & IF(A1>=120,"s",""),"") & IF(MOD(A1,60)>0,IF(A1>=60,", ","") & MOD(A1,60) & " minute" & IF(MOD(A1,60)>1,"s",""),""))

根据评论,您使用的是德国 Excel,它使用分号分隔函数参数,因此请用分号替换逗号:

=IF(A1=0;"0 minutes";IF(A1>=60;FLOOR(A1/60;1) & " hour" & IF(A1>=120;"s";"");"") & IF(MOD(A1;60)>0;IF(A1>=60;", ";"") & MOD(A1;60) & " minute" & IF(MOD(A1;60)>1;"s";"");""))

(注意:那里仍有一个逗号,因为它在输出字符串中。)

0      0 minutes
1      1 minute
2      2 minutes
59     59 minutes
60     1 hour
61     1 hour, 1 minute
62     1 hour, 2 minutes
119    1 hour, 59 minutes
120    2 hours
121    2 hours, 1 minute
122    2 hours, 2 minutes
179    2 hours, 59 minutes
180    3 hours
181    3 hours, 1 minute
182    3 hours, 2 minutes
1439   23 hours, 59 minutes
1440   24 hours

答案2

假设您的数据在 A 列。在 C2 中写入此公式并向下填充以将分钟转换为天数的小数(这是 Excel 存储时间的方式):

=A2/1440

笔记正如您在上面的评论中发现的那样,要将十进制分钟转换为时间格式,您可以应用此24*60公式1440

将分钟转换为 Excel 处理时间的形式后,您可以简单地使用自定义时间格式来显示它。选择数据范围并应用此单元格格式:

h "hours, " m "minutes"

请注意,Excel 只会将少于一整天的时间视为小时,因此只要时间不超过 24 小时,这种方法就会有效。

或者,您可以使用 HOUR 和 MINUTE 函数提取时间成分。在 D2 中,您可以使用:

=HOUR(C2)&" hours, "&MINUTE(C2)&" minutes "

你得到,

在此处输入图片描述

再次强调,这仅限于少于一天的时间部分。如果您可以有超过一天的时间,请添加天数:

=INT(C2)&" days, "&HOUR(C2)&" hours, "&MINUTE(C2)&" minutes "

现在你明白了,

在此处输入图片描述

相关内容