Excel 日期和时间公式-对单元格值进行四舍五入

Excel 日期和时间公式-对单元格值进行四舍五入

我正在为我工​​作的图书馆使用的集体学习室预订系统准备一份使用情况统计报告。我已将过去一年 MySQL 中的所有预订信息导出到 Excel 中,并试图得出所有预定预订的平均数字。

我用来计算每个预约的开始时间和结束时间之差的公式(=TEXT[end_time]-[start_time],"h:mm")效果很好,但我遇到了一个问题。预约的结束时间列为 00:29(半点结束),00:59(整点结束)。

我的电子表格中有几千行,我不想使用“查找和替换”将这些值更改为 00:30 和 00:00。有没有办法修改我的公式,以便它能够四舍五入结束时间,或者可能在公式中将结束时间加一分钟。

答案1

根据 Iszi 的评论进行编辑:

end_time - start_time + TIME(0,1,0)

TIME() 函数的文档

原始答案:

您可以在 end_time - start_time 计算中添加 1 分钟,因为在 Excel 中 1 天等于整数 1,因此您的公式将如下所示:

end_time - start_time + 1/24/60

(除以24小时60分钟)。

答案2

您无需数学运算即可完成。对于 A1 中的开始时间和 B2 中的结束时间:

=SUBSTITUTE(SUBSTITUTE(TEXT(B1-A1,"h:mm"),":29",":30"),":59",":00")

SUBSTITUTE 是一个相当简单的文本替换函数。它需要三个参数:

  1. 原文
  2. 要替换的字符串。
  3. 替换字符串。

代替仅有的当与参数 2 匹配时替换文本,然后它仅有的替换匹配的文本 - 字符串的其余部分保持不变,而根本没有匹配的字符串则保持不变。

因此,我们从上面的问题开始,计算原始时间。该时间将传递给 SUBSTITUTE,后者检查“:29”并替换为“:30”。(如果未找到“:29”,则不执行任何操作。)然后将生成的字符串传递给最外层的 SUBSTITUTE,后者检查“:59”并根据需要替换为“:00”。

答案3

如果您的开始和结束时间恰好在整点和半点,您的公式不应该返回 29 或 59 分钟的持续时间。我预计数据库导出可能还包含一些秒数。我们可以完全消除秒数,并计算假设开始/结束时间为每分钟零秒的持续时间,如下所示:

(开始于 A1。结束于 B1)

=TEXT(TIME(HOUR(B1),MINUTE(B1),0)-TIME(HOUR(A1),MINUTE(A1),0),"h:mm")

TIME 是一个函数,它将使用给定的小时、分钟和秒生成 Excel“序列号”,然后以标准时间格式显示它。“序列号”是 Excel 在后端存储日期/时间值的方式。当您在8/1/2014 15:26:57已配置日期/时间格式的单元格中看到类似内容时,Excel 实际上会将值存储为41852.64372- 这表示自大约以来的天数(小数部分是一天的分数)1/1/1900 0:00:00(它比这稍微复杂一些,但这已经超出了您需要知道的范围)。

HOUR 和 MINUTE 是函数(是的,还有 SECOND - 以及 DAY、MONTH 和 YEAR (如果您需要的话)),它们从日期/时间序列号中返回各自的部分。

因此,我们上面所做的就是从 A1 和 B1 中获取时间,将每个时间中的秒数清零,然后然后做减法。

相关内容