Inconsistent results from excel rows

Hi folks,

With the help of many other forum users I have created a great time sheet. I was playing around with it and it yields in consistent results. I have attahed an example sheet for you to see.

Paramaters
When there are exactly 2 hours or less worked the result should be 3 hours of STO. One minute over that total and it should go to THO for the first 2 hours and DTO for any time after the 2 hours. Why do the various rows yeild different results?

If you notice all entries are 2 hours some one minute above and below. Excel seems to have difficulty managing the threshold of 2 hours as some entries are correct, others are THO (rows 14, 17, 20, 23, 26, 29), one is DTO (row 13) yet all the formulas are exactly the same. It is every 3rd row, not sure what causes this.

Any suggestions? Any help is greatly appreciated.

Russell

For some reason this does not change the results. Interesting how it is not every row?

Hey Ford,
Thanks for the help. I am relatively new to the complexity of these formula's. What I am trying to design with the help of lots of forum users is a TS where I can simply enter my times and it automatically calculates the appropriate STO, THO, and DTO. at the end of the pay period I simply take my totals and enter those into my pay system and get paid out. Is there a simpler formula you are aware of?

Thanks,
Russ

For some reason I cannot make some of these formula's work, it appears to do the same thing around the 2 hour threshold. One minute over or under the 2 hour threshold and it works well. Exactly 2 hours appears to be difficult for excel. When I changed the formula for I14, it did not calculate any values at any point. Excel gives the error message “error in value” for the formula in J14 as well. I changed the formula’s, formatting and copied down. I may be doing something wrong.
Thanks,
Russ

Hey Bernie,
I have downloaded your version and the parameters I am looking for are as follows:

When there are exactly 2 hours or less worked the result should be 3 hours of STO. One minute over that total and it should go to THO for the first 2 hours and DTO for any time after the 2 hours.

Unfortunately where I work they require "exact time" so all entries must be exact. In your sheet when the times areentered at exactly 2 hours rows 13-24 result in 2 hours of DTO and rows 25-30 result in THO. All entries with exactly 120 minutes or less must result in 3 hours of STO and anything 121 minutes and up need to result in the first 120 minutes being THO and anything over 121 minutes being DTO. For example, I work 45 minutes and enter my time of 10 Pm to 10:45 Pm I would get paid 3 hours of STO. If I were to work 2' 45" and I enter my time from 4Pm to 6:45PM then the result should read 2 hours THO and 45 minutes of DTO. If I were to work 121 minutes it should result in 120 minutes of THO and 1 minute of DTO.

I am I being too exuberant? Perhaps what I am asking is too much for excel to handle? Maybe I just have to revert to a manual entry of time and the resulting OT will be calculated based on the manual entry of "worked" time entry.

I really appreciate your efforts in trying to help me so please dont take this the wrong way, it is just the way my employer tracks OT.

Thanks,
Russ

That is getting closer, but there continues to be problems with exactly 2 hours as it lists 3 hours STO and 2 hours of THO. It should only list 3 hours of STO. It works great one minute over or under the 2 hour mark. Excel just really appears to struggle with any time listed exactly at 2 hours.

That is true, I did put in times and copy down. I have also tried manually entering the times and it yields the same results. For some reason, the vast majority of any entries of exactly 2 hours excel does not deal with well. If it is one minute under (119 minutes) or one minute over (121 minutes) excel does a great job of those, but exactly 120 minutes it cannot handle. I have no explanation.

Thanks for the look and effort though,
Russ

Originally Posted by Russ15

Any suggestions? Any help is greatly appreciated.

I think the worksheet "fix" in the file "russ fixed.xlsx" (click on the file name) does what you intended. The formulas:

Please Login or Register  to view this content.

In Russ'soriginal design, columns G:J represented hours as a decimal number. There is no need to change that, as FDibbins did. In fact, I think the change to hh:mm representation complicates the arithmetic.

The formulas in columns C and E ensure that time is rounded to the minute with the correct binary representation. That was part of Russ's original problem: the unrounded time arithmetic (Fill Series) created infinitesimal binary anomalies.

The formula in column G is essentially the correction that Bernie suggested: replacing SUM with ROUND. Bernie's change did not seem to work as intended because of mistakes in Russ's original formula, to wit:

1. In column H, =IF(G11=0,0,IF(G11<2,3,0)) should be =IF(G11=0,0,IF(G11<=2,3,0)).

2. The formula in I11, =IF(G11>2,MIN(2,G11),""), is changed to =IF(G25>=2,MIN(2,G25),"") starting in I25.

The N() function in columns H:I allow for the value in column G to be the null string ("").

I believe the formulas in columns H:I resolve ambiguities in Russ's description. But only Russ can confirm that. The ambiguities are described below.

In another discussion, Russ wrote:

Originally Posted by Russ15

If you work 2 hours or less you get paid 3 hours of straight time over time (STO)

If you work 2 hours or more you get paid the first 2 hours at 1 1/2 (1.5X) times (THO) and the rest at 2X (DTO).

For exactly 2 hours, there would be results in both STO and THO. I don't believe that is the intention.

In fact, in this discussion, Russ wrote:

Originally Posted by Russ15

When there are exactly 2 hours or less worked the result should be 3 hours of STO.

One minute over that total and it should go to THO for the first 2 hours and DTO for any time after the 2 hours.

Thus, the formula in column H is =IF(N(G11)=0,"",IF(G11<=2,3,"")).

And the formulas in column I:J are =IF(N(G11)>2,2,"") and =IF(N(G11)>2,ROUND(G11-2,2),"").

However, there is still an ambiguity. Russ wrote "one minute over that". But in fact, his original formula rounds to the nearest quarter-hour.

So in fact, 7 minutes or less over 2 hours is rounded to 2 hours. So 3 hours of STO is recorded. See row 31.

But 8 minutes or more over 2 hours is rounded to 2.25 hours or more. So THO and DTO time are recorded. See row 32.

Finally....

It seems odd to me that effectively time-and-one-half (3) is record in STO, but just time (2) and time-over-two are recorded for THO and DTO.

Originally Posted by Russ15

That is true, I did put in times and copy down. [....] For some reason, the vast majority of any entries of exactly 2 hours excel does not deal with well. If it is one minute under (119 minutes) or one minute over (121 minutes) excel does a great job of those, but exactly 120 minutes it cannot handle. I have no explanation.

See the comment about unrounded time arithmetic above. If that is not sufficient, I can explain in more detail.

In a nutshell, Excel time is represented as a fraction of a day. And with the binary representation that Excel (and most applications) uses internally, most non-integer values cannot be represented exactly. These infinitesimal differences cause the result of most arithmetic to be different from mathematical expectations.