1. ## Another Railroad Time formula (97) (1997)

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

2. ## Re: Another Railroad Time formula (97) (1997)

You didn't attach a file

Steve

3. ## Re: Another Railroad Time formula (97) (1997)

Thought Idid, Here it is again.

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

Attachment did not work

12. ## 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. ## 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. ## 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. ## 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 Last

#### Posting Permissions

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