不含工作周的日期和时间之间的差异 Excel [英] Difference between date and time w/out work week Excel

问题描述

我正在尝试确定程序所需的时间.我有开始日期和开始时间以及结束日期和结束时间.我需要做的是以小时:分钟:秒显示结果，但我需要使用 7 天工作周而不是 5 天工作周.这是一个示例数据点:

I am trying to determine the amount of time a procedure takes. I have the Start Date and Start Time as well as End Date and End Time. What I need to do is display the result in hours:minutes:seconds BUT I need to use a 7 day work week rather than a 5 day work week. Here is a sample data point:

单元格 A1 和 C1 都被格式化为短日期.单元格 B1 和 D1 被格式化为时间.E1 中的公式为 =((C1+D1)-(A1+B1)) 并且 E1 的格式为自定义数字 [h]:mm:ss .我遇到的问题是计算整个周末.时间不会加起来，我认为这是因为 Excel 使用的工作周类型.

Cell A1 and C1 are both formatted as short dates. Cell B1 and D1 are formatted as times. The formula in E1 is =((C1+D1)-(A1+B1)) and E1 is formatted as a custom number [h]:mm:ss . The problem I am having is calculating through a weekend. The times just don't add up and I think it is because of the type of work week that Excel uses.

感谢任何帮助，谢谢！

推荐答案

首先，您需要知道/理解任何数字对于 Excel 来说都是潜在的日期/时间，而 Excel 只知道 datetime(并且不是日期和时间分开).

First, you need to know / understand that any number is a potential date / time for Excel and that Excel knows only datetime (and not date and time separately).

原因如下:小数位占位符之前的任何内容都是自 1899 年 12 月 31 日以来的天数.因此，数字 42,000.00 也是一个日期.这是 1899 年 12 月 31 日之后的第 42.000 天，也就是 2014 年 12 月 27 日.

Here is why: anything before the decimals-placeholder are the days since December 31, 1899. So, the number 42,000.00 is also a date. It is the 42.000th day after December 31, 1899 which is December 27, 2014.

小数位占位符之后的任何内容都被视为一天的一小部分.因此，值 0.5 是半天，因此时间是中午 12 点，而 0.75 是下午 6 点.

Anything after the decimal-placeholder is considered a fraction of a day. So, the value 0.5 is half a day and therefore the time 12 noon while 0.75 is 6 in the afternoon (pm).

因此，您在上述示例中显示的所有日期也可能具有与其关联的时间，并且您显示的所有时间也可能具有与其关联的日期.只需将所有这些单元格上的数字格式更改为通用数字，您应该只看到日期的 Integer 值(没有任何小数位)，而所有时间都应该只包含 1 的一小部分，并且应该所有的小数位占位符前都有一个 0.

Hence, all of the dates you are showing in your above examples may have also times associated with them and all the times you are showing might also have a date associated to them. Just change the number format on all of these cells to general numbers and you should see only Integer values for the dates (without any decimal places) while all the times should contain only be a fraction of 1 and should all have a 0 before the decimal-placeholder.

如果情况并非如此，那么您刚刚找到了罪魁祸首.

If that is not the case, then you have just found your culprit.

有了以上知识，计算两个日期或时间之间的差异也很容易.区别在于简单的加减法:42,000.00 + 100 + 0.5 = 2014 年 12 月 27 日中午 12 点之后的 100 天 = 2015 年 4 月 6 日中午.因此，您可以像所做的那样将数字相加并减去它们.如果你想减去两天来计算一个周末，那么你可以简单地减去 2.

With the above knowledge it is also easy to calculate the difference between two dates or times. The difference is a simple addition or subtraction: 42,000.00 + 100 + 0.5 = 100 days after December 27, 2014 at 12 noon = April 6th, 2015 @ noon. So, you can just add the numbers and subtract them as you have done. If you wish to subtract two days to account for a weekend then you can simple subtract 2.

现在，让我们看看上面的例子:2016 年 6 月 23 日转换为 42,544，而早上 07:41 转换为 0.32.所以，我们这里有 42,544.32 而结尾的 datetime 转换为 42,545.47.

Now, let's have a look at your above example: June 23, 2016 converts to 42,544 while 07:41 in the morning converts to 0.32. So, we have here 42,544.32 while the ending datetime converts to 42,545.47.

差值正确计算为 1.15.这意味着有一天的差异和一天的分数 0.15 * 24(一天中的小时数)= 3 小时 36 分钟.

The difference is calculated correctly as 1.15. That means that there is a difference of one day and the fraction of a day of 0.15 * 24 (hours in a day) = 3 hours and 36 minutes.

我猜您遇到的真正问题是正确显示结果.如果将其显示为日期，则它是 1899 年 12 月 31 日 = 1900 年 1 月 1 日之后的一天.如果将结果显示为时间，则只能看到 03:36:00.最好的方法可能是将差异显示为仅作为 1.15 的数字.如果您想以不同的方式查看结果，则必须根据需要调整结果视图.例如:= 1.15 * 24 = 开始日期/时间和结束日期/时间之间的 27.6 小时差异.

I guess the real problem you are having is to show the result correctly. If you show it as a date then it is one day after December 31, 1899 = January 1st, 1900. If you show the result as a time then you only get to see the 03:36:00. The best is probably to show the difference as a number only as 1.15. If you want to see the result differently then you will have to adjust the resulting view to your needs. For example: = 1.15 * 24 = 27.6 hours difference between the start date/time and the end date/time.

如果您有任何其他问题，请告诉我.

Let me know if you have any further questions.

这篇关于不含工作周的日期和时间之间的差异 Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！