Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Oroville, California, Uruguay
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Another Railroad Time formula (97) (1997)

    Please see the attached file.

    I want to take column (L (Overtime in Hours and Minutes) and tag it to R (Overtime startr After

  2. #2
    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: Another Railroad Time formula (97) (1997)

    You didn't attach a file

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Oroville, California, Uruguay
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another Railroad Time formula (97) (1997)

    Thought Idid, Here it is again.

  4. #4
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Oroville, California, Uruguay
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another Railroad Time formula (97) (1997)

    Thanks Steve: Looks good so far. I will run it through the paces for bugs. I have added a new column (M) to reflect total time (straight time + overtime). Will have to add another column, as the Federal Law does not allow us to be "ON Duty" (on a train) for more than 12 hours. We CAN have more than 12 hours pay, though. If we "DOL" (Die - expire our service time - 12 hours) enroute, we get paid the aditional time to our off duty place and time at the overtime rate. If you want to take the liberty of adding a new column to reflect this along with the formula, I would appreciate it.

    Thanx
    Excellent Excellent work. I have been working on this project for months and months. I have gotten more results in the few days on this forum in all the prior months combined. Just wish I could send the whole timesheet for perusal, but alas, it's too large.

    Larry

  5. #5
    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: Another Railroad Time formula (97) (1997)

    <P ID="edit" class=small>(Edited by sdckapr on 16-Nov-03 13:40. Added formula to round OT to nearest minute)</P>Well here is try number 1:

    Put this in R3 format in "[h]:mm" [I put it into "excel time" by dividing by 24]
    <pre>=S3/((I3<101)*12.5+(I3>=101)*(I3<301)*16.25)/24</pre>


    This is a better calculation for your current K3 (I assume straight time is still the same calc and logic as you use.)

    <pre>=IF(ISBLANK(A3),"",MIN(G3-C3+(C3>G3),1/3))</pre>


    For Overtime, try this in L3:
    <pre>=IF(ISBLANK(A3),"",MAX(G3-C3+(C3>G3)-IF(S3<(100+(I3>100)*30),8/24,R3),0))</pre>


    The logic is:
    If Col I (Routing Code) <=100 then the miles "cutoff" is 100
    otherwise if 100< Col I (Routing Code) <=300 then the miles "cutoff" is 130
    [This is 100 (+ 30 if I3>100)]
    If col S (Miles Run) is less than the cutoff, calculate OT as the hours over 8 hours.
    Otherwise, calc as the amount of hours over the "OT Start hours" calc in Col R

    My formulas calculate No overtime in most of your examples. Row 3 has the same as listed, and row 6 has 3 min 42s of OT.

    Steve
    PS If you want to round to the nearest minute use the formula:
    <pre>=IF(ISBLANK(A3),"",INT(MAX(G3-C3+(C3>G3)-IF(S3<(100+(I3>100)*30),8/24,R3),0)*24*60+0.5)/24/60)</pre>


  6. #6
    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: Another Railroad Time formula (97) (1997)

    Isn't M3 just:
    <pre>=K3+L3</pre>


    Or are you looking for:
    <pre>=min(K3+L3,12/24)</pre>


    Or am I missing something obvious?

    Steve

  7. #7
    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: Another Railroad Time formula (97) (1997)

    Or maybe it is just the elapsed time:
    <pre>=G3-C3+(C3>G3)</pre>


    Or even:
    <pre>=min(G3-C3+(C3>G3),.5)</pre>


    Steve

  8. #8
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Oroville, California, Uruguay
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another Railroad Time formula (97) (1997)

    Will take a look. See attached. Column T in my example is in another sheetin the working copy. I made the example for size restraints. . Can't figure out how to reference it as shown in this formula. o429:s728, column 5 in the Pay Scales sheet is where I keep all references as shown in column T.
    =IF(ISBLANK(A3),"",IF(ISBLANK(Q3)=TRUE,"",VLOOKUP( Q3,'Pay Scales'!$O$429:$S$728,5)))

    Larry

  9. #9
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Oroville, California, Uruguay
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another Railroad Time formula (97) (1997)

    Yes, it is K3+L3. I will have to figure out either a new column for total time, and keep Federal time to a minimum of 12 hours, or something. Will take a look and see which route is more esthetically appealing, and efficient.

  10. #10
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Oroville, California, Uruguay
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another Railroad Time formula (97) (1997)

    Steve: Is there any way to make column N in the atached to reflect just hours and minutes instead days, hours and minutes.? I fave tried to format the cells, but cannot seem to find the correct format.

    Thanx

    Larry

  11. #11
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Oroville, California, Uruguay
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another Railroad Time formula (97) (1997)

    Attachment did not work

  12. #12
    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: Another Railroad Time formula (97) (1997)

    If you want elapsed time (can go over 24 hours) use the format (with the brackets):
    [hh]:mm


    Steve

  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: Another Railroad Time formula (97) (1997)

    You have no attachment with the formula, so I will make a guess.
    Do you want this?
    <pre>=IF(or(ISBLANK(A3),ISBLANK(Q3)),"",VLOOKUP(Q3 ,'Pay Scales'!$O$429:$S$728,5,FALSE))</pre>


    I got rid of "isblank(Q3)=true" as is redundant "isblank(q3)" gives true/false

    I added the FALSE parameter to the lookup, assuming you wanted to find an exact match. If you don't have this and your list is not sorted it can give strange results.

    If not right, more details about what you are looking up.

    Steve

  14. #14
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Oroville, California, Uruguay
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Another Railroad Time formula (97) (1997)

    Steve: Still having problems with Straight time and Overtime.

    In the attached example, I have highlited row 6 in yellow and purple. The cells that are in purple are the ones that need attention.

    Total time for this trip should be 22 hours. Overtime, based upon cell U6, should be around 4 minutes (the company will round that up to 5 min. If it were <3, they would round down. They pay in 5 min. encriments.

    So, since the government only allows us to work a maximum of 12 hours, straight time in this scenario should be 11 hours 55 minutes and overtime should be only 5 minutes (calculated per cell U6.

    I have added a new column (O) in which the time over 12 hours (L&M can only be max 12 hours) should be placed so I can compute time. This new column will also be at the overtime rate.

    Thanx

    Larry

  15. #15
    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: Another Railroad Time formula (97) (1997)

    Well I am confused.

    1) You are correct, Total On Duty time is 22 hours: it is calculated using:
    =(H6-C6+(C6>H6))

    That was one of my suggestions for Col N.


    2) I thought straight time was a max of 8 hours, which is what K currently calcs. If K can be >8 hours why isn't row 3 9hrs and 59 min of straight time? What conditions allow straight time over 8 hours?

    3) Currently you get in L, 10:03 hrs which is the amount of time over the time in Col U (22 - 11:56:18 = 10:03:42) which is what (I thought) you wanted.

    4) It seems like you want different calcs if the "elapsed work time" [=(H6-C6+(C6>H6))] is > 12. If that is a further condition for K and L, you will have to give more details.

    5) We have been working a lot with "mega-formula". As in the earlier problem, you might find it useful to just make the intermediate calcs and refer to those. It might make the formulas easier to work with. If you notice in the formulas we use the "elapsed time" alot. it might be better to just list it explicitly so we can use it in the calcs instead of recaculating it in many formulas. It makes the formulas shorter, improves calc time, and makes logic clearer.

    Steve

Page 1 of 3 123 LastLast

Posting Permissions

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