Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    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

  2. #2
    Platinum Lounger
    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.
    Gre

  3. #3
    Platinum Lounger
    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

  4. #4
    2 Star Lounger
    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

  5. #5
    2 Star Lounger
    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

  6. #6
    Platinum Lounger
    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?
    Gre

  7. #7
    2 Star Lounger
    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

  8. #8
    Platinum Lounger
    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.
    Gre

  9. #9
    2 Star Lounger
    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

  10. #10
    2 Star Lounger
    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

  11. #11
    WS Lounge VIP sdckapr's Avatar
    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

  12. #12
    2 Star Lounger
    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 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. #13
    WS Lounge VIP sdckapr's Avatar
    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 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
  •