Results 1 to 13 of 13
Thread: Railroad Overtime Problem (1997)

20031224, 01:13 #1
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20031224, 12:39 #2
 Join Date
 Nov 2001
 Location
 Vienna, Wien, Austria
 Posts
 5,009
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.Grüße

20031224, 16:02 #3
 Join Date
 Jan 2001
 Posts
 3,788
 Thanks
 0
 Thanked 1 Time in 1 Post
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

20031227, 02:46 #4
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Railroad Overtime Problem (1997)
Here is another attachment. I hope it fits.
Larry

20031227, 02:55 #5
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20031227, 10:51 #6
 Join Date
 Nov 2001
 Location
 Vienna, Wien, Austria
 Posts
 5,009
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?
Grüße

20031227, 21:16 #7
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20031227, 23:50 #8
 Join Date
 Nov 2001
 Location
 Vienna, Wien, Austria
 Posts
 5,009
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.
Grüße

20031228, 03:22 #9
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20040205, 03:28 #10
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
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

20040205, 13:12 #11
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20040205, 16:01 #12
 Join Date
 Nov 2003
 Location
 Oroville, California, Uruguay
 Posts
 105
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 paystub, 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

20040205, 16:26 #13
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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 timeandahalf) 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 timeandahalf) 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