日期与时间的差异，不在工作周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.

任何帮助不胜感激，谢谢！

Any help is appreciated, thanks!

推荐答案

首先，您需要知道/了解任何号码是一个潜在的日期/时间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点，而在下午（pm）的时候是0.75。

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).

因此，你显示的所有日期在上述例子中可能还有与之相关的时间，并且您显示的所有时间也可能与之相关联。只需将所有这些单元格的数字格式更改为常规数字，您应该只看到整数的值（不带任何小数位），而所有的时间都应该只包含一小部分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日中午12点后= 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.

现在，我们来看看你上面的例子：
June 23，2016转换为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屋！