1. ## Railroad Overtime Problem (1997)

I have a spreadsheet that uses an extensive lookup table to glean figures from (see attached). Columns AO and AP combine to compute total overtime made. Column AX shows the dollar amount made before going on overtime. Column AY (this is where my problem arises) is supposed to show the amount of monies made on overtime.

In this scenario, column AX yields \$183.78 (which represents a days pay at straight time. Column AY should yield \$137.84, but it calculates to \$0.51. HELP!

Larry

2. ## Re: Railroad Overtime Problem (1997)

Can you have a look at your attachment? There appear to be a fair number of columns missing. Based on your posting - and what is there - it seems that theree should be data up to AZ. It in fact stops well short of there.

BTW I should add that <!profile=sdckapr>sdckapr<!/profile> is going to be far more likely to be able to respond in full than I am. I was merely taking a quick look in his absence.

3. ## Re: Railroad Overtime Problem (1997)

If I understand the spreadsheet correctly then the following will work (am I right in assuming overtime is paid at time and a half?)

V3+W3 is overtime hours worked (4 hours in this case so V3+W3 = 0.16666666 which is the proportion of 1 day)
Cell AE3 (Straight Time/Trip Rate Monies Made) is in this case \$183.78

So your answer is 4 hours overtime at time and a half, where a full days pay is \$183.78

Therefore cell AF3 (Overtime Pay Monies Made) can be simplified to =AE3*(V3+W3)*3*1.5

(V3+W3)*3 - as it is based on an 8 hour day, you need to multiply by 3

4. ## Re: Railroad Overtime Problem (1997)

Here is another attachment. I hope it fits.

Larry

5. ## Re: Railroad Overtime Problem (1997)

The problem here is that overtime does NOT always start after 8 hours. Look at column AT in my latest attachment. Overtime can start anywhere from 8 hours up to and including 11 hours 55 minutes.

Thanks

Larry

6. ## Re: Railroad Overtime Problem (1997)

Thanks very much for the Update. In your original attachment, the values that you mentioned as troubling you appeared in AE and AF. (Your post mentioned AX and AY.) In your new attachment these columns seem to have become BE and BF. BE and the old AE have the same formula, but - in the "problem" column the BF formula is not the same as the old AF. BF does, however, yield the value that you were originally seeking. Have you solved the issue - or is the present formula just a forcing fix?

7. ## Re: Railroad Overtime Problem (1997)

I attached all of the superfluous cells that were missing, and readjusted the formula per suggestion in above reply's. My problem here is that I don't understand the concept. Overtime does NOT always start after 8 hours. Depending upon the run, overtime can start anywhere from 8 hours, up to and including 11 hours and 59 minutes (hence two different cells that compute overtime).. It was suffested that I multiply by 3. This seem to have remedied the problem in the example. Since I don't understand the concept, I don't know if this will work or not.

Thanks

Larry

8. ## Re: Railroad Overtime Problem (1997)

In Cell BF3, if you substitute *(1/AT3) for *3, you get the same answer in your new sheet. This formula will now depend on the number of straight time hours worked. Can you let us know whether this now produces the range of numbers that you're looking for? If it does not, please post back to this same thread.

9. ## Re: Railroad Overtime Problem (1997)

I have applied the recommended change, and it appears to be working. I will let you if there is a problem when I get a chance to view my follow employees racapitulation of their pay.

Thanx

Larry

10. ## Re: Railroad Overtime Problem (1997)

Well, my formula is not working properly

=IF(B3="","",IF(OR(A3="",AR3=0),"",IF(S3=TRUE,"",V LOOKUP(S3,'Cocuments and SettingsLarry WitcherMy DocumentsRailroadTimesheet[Timebook (Target) Master.xls]Pay Scales'!\$E\$3:\$L\$302,6)*(AT3+AU3)*(1/AR3)*1.5)))

AR3 = 11hrs 55 min, AT3 = 0:05 min, AU3 = 2hrs 30 min, Pay Scales'!\$E\$3:\$L\$302,6 = \$119.39. \$119.39 represents 8 hours pay. When the formula is completed, the final answer in cell BD3 shouldd be \$72.83 for 2 hrs 35 min overtime. Please see the attached spreadsheet for reference. Please help.

Thanx

Larry

11. ## Re: Railroad Overtime Problem (1997)

I don't understand.
If \$119.39 is 8 hrs pay then they are paid \$14.92 / hr or (at time and half) = \$22.39/hr. 2:35 hrs is only \$57.83 not \$72.83. \$72.83 is 3:15 hrs of overtime.

I also don't see why the 11:55 hrs (from AR3) is even in the formula.

Could you explain how you think the numbers should be calc'd?

Steve

12. ## Re: Railroad Overtime Problem (1997)

Steve:

Refer to post 325615, at that time column AR was AT ( eliminated two columns from the spreadsheet because of contractual changes I was unaware of). When a crew starts overtime is constantly changing, depending on what job they work. Column AR computes when overtime will start.

The crew went on duty @ 05:00 AM and off duty @ 19:30, and did not go on overtime until after the expiration of 11hrs 55min (cell AR3), receiving 2hrs 35min overtime (cells AT=0:05 + AU=2:30). The individual was paid \$72.83 for this overtime. The rate is based upon \$119.39 (Pay Scales'!\$E\$3:\$L\$302,6 = \$119.39), unless I have based it upon the wrong rate, which is possible. These formulas (column BD and column BE have been driving me crazy, (BE4 should compute out to be \$100.30 based on 5hrs 10 min held Cell AW4). These figures came directly from a friends pay-stub, as I have not received any overtime or held at away terminal pay. These two formula's have been driving me craze, and I have tried over and over to figure out what the problem is, hence not posting for a short while.

Hopefully with the proper dollar amounts paid, you can come up with the proper answer.

Larry

13. ## Re: Railroad Overtime Problem (1997)

You will have to give more details about how it is derived.

How would you calc to get \$72.83?
If he worked 2:35 of OT (at time-and-a-half) it should be \$57.83
To get \$72.83 he either worked 3:15 OT @ \$119.39 / 8 hrs or 2:35 @ 150.36 / 8 hrs or there is something else in the calc you have failed to include and tell us

How would you calc to get \$89.55?
If he worked 3:10 of OT (at time-and-a-half) it should be \$70.89
To get \$89.55 he either worked 4:00 OT@ \$119.39 / 8 hrs or 3:10 @ 150.82 / 8 hrs or there is something else in the calc you have failed to include and tell us

How is "held away pay calc'd"? He worked 5:10 for \$100.30 that is \$155.30 / 8 hr (if straight time) or \$103.54 / 8 hr (if OT @ 1.5x)

We can't even begin to give a formula that would give the correct numbers unless you tell us how they are calculated.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•