☰

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

tag-icon • tag-icon microsoft-excel worksheet-function date-time
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 中获取时间，将每个时间中的秒数清零，然后然后做减法。

相关内容