计算工作时间和工作超时时Excel中的奇怪行为。 [英] Strange behavior in Excel when counting Work Hours and Work Super Hours.
问题描述
我组成了下一个工作表，以计算我的工作时间。
I composed the next Worksheet in order to count my Workhours.
通过互联网提出的其他解决方案将无法正常工作，因为每天的Workhours数量并不是固定的。当总工作时间等于正常工作时间时，这个问题有时会出现。当我尝试使用条件格式化超级小时单元格时，我没有得到正确的结果。平等似乎并非如此。
Other solutions proposed over the internet would not work, since the Workhours amount per day was not fixed at first. The problem makes its appearrance sometimes when the "Total Work Hours" equal the "Normal Work Hours". When i try to format the Super Hours Cells using conditions, i don't get the correct result. The equality seems not to be the case.
单元格K9：负数或非常大的日期和时间值显示为磅符号（###）
Cell K9: Negative or very big date and time values are displayed as pound signs (###)
以下信息很重要：
- 单元格A4：B500，F4，F5格式如下：小时13:30
- 单元格J7：R9格式为：Custom [ h]：mm
- J7：= SUM（$ C4：$ C34），分别为单元格K7，L7 ...
- J8：= J4 * $ F $ 5，分别为单元格K8，L8 ...
- J9：= IF（J $ 7> = J $ 8; J $ 7-J $ 8; J $ 8-J $ 7）细胞K9，L9 ...
- The Cells A4:B500, F4, F5 are formated like: Hour 13:30
- The Cells J7:R9 are formated like: Custom [h]:mm
- J7: =SUM($C4:$C34) and respectively Cells K7,L7...
- J8: =J4*$F$5 and respectively Cells K8,L8...
- J9: =IF(J$7>=J$8;J$7-J$8;J$8-J$7) and respectively Cells K9,L9...
我想知道为什么会发生这种情况，如果有解决方案或不同的方法。 / p>
I would like to know why this happens and if there is a solution or a different approach.
推荐答案
当您在Excel中执行数学操作时，有时会得到浮点错误。他们来了，因为Excel被限制在15位精度，所以15以后的有效数字被忽略。
When you perform math operations in Excel you will sometimes get floating point errors. They come about because Excel is limited to 15 digit precision so significant digits after 15 are ignored.
Excel中的时间都是几天的百分比。计算机以二进制计算，所以所有这些小数必须在数学运算之前转换为二进制，结果必须转换为十进制。
Times in Excel are all percentages of days. Computers calculate in binary so all those decimals have to be converted to binary before the math operation and the result has to be converted back to decimal.
例如，简单的像二进制中的0.1一样，像十进制的Pi - 它不是一个有限的数字。
For example, something as simple as 0.1 in binary is like Pi in decimal - it isn't a finite number.
对这些转换的数字进行数学运算，并且将出现舍入误差。
Do math on those converted numbers and there are going to be rounding errors.
如果您更改格式的K9给你，你可能会发现它是一个非常小的负数，以科学记数表示。
If you change the format of K9 to General, you will probably find it's a very small negative number expressed in scientific notation.
将K9中的结果舍入大于4的值，以允许1440分钟在一天中，应该摆脱错误。
Rounding the result in K9 to something greater than 4 to allow for 1440 minutes in a day, should get rid of the error.
这篇关于计算工作时间和工作超时时Excel中的奇怪行为。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
