Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Lounger
    Join Date
    Nov 2004
    Location
    Oroville, California, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Figuring time (1993)

    I would like to get a formula to compute time away from home. Please see attached sample. If I left X location on day one, and returned back to X on day 3, I would liken the formula to compute the total hours and minutes spent away from home. I would like to also keep a running total in another cell of the total accumulated time spent away from home. Also, in another cell I would like to take the accumulated time spent away from home, and convert that to days. This is for US tax purposes. I ultimately need to know how many days, etc.that I spend away from home to compute expenses.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Figuring time (1993)

    I don't understand the comments in the workbook you attached. Can you explain exactly which columns should be used? And what is the meaning of the value <code>)6;00</code> in cell I4?

  3. #3
    Lounger
    Join Date
    Nov 2004
    Location
    Oroville, California, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Figuring time (1993)

    In my example, I went on duty at the home terminal (Stockton) and off duty at Keddie. I went on duty again at Keddie, and off duty at Stockton. The time that I am looking for is from the time I go on duty at my home terminal (this example Stockton (I have a column designation the home terminal) off duty when I get back to my home terminal (Stockton). I have two example samples shown, and the time that I am looking for is a total amount of time for trip which started and ended at the home terminal (Stockton). What may throw you is a side trip before coming back home, theu wanting a total amount of time for a trip Going on duty and off duty at Stockton (or home terminal as shown in example).

    Column "K" is where I want the hours computer per trip, and Cell L3 is where I would keep a running total of the total tiime spent away from home. I also should make another column (passibly "M") to divide the running total by 24 in order to keep track of the amount of trips made. The IRS requires that this information be used on their form 2106.

  4. #4
    Lounger
    Join Date
    Nov 2004
    Location
    Oroville, California, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Figuring time (1993)

    The meaning of the value )6;00 in cell I4 was a typo. Sorry about that. That should be 05:00 in order to make the time come out to 29 hours..

  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: Figuring time (1993)

    <P ID="edit" class=small>(Edited by sdckapr on 09-Jan-05 07:19. Added PS after a little thinking/analysis of Sheet)</P>In Cell L3 what value do you calculate?
    Can you explain the "logic" in the calculation based on the example data.

    Could you do the same for the values you obtain manually in L4:L7 (values and the logic).

    Steve
    PS
    Just a stab at what you are after. How about in K3:
    <pre>=(IF(H3=M3,A3+I3,A3+1)-IF(D3=M3,A3+E3,A3))*24</pre>


    and In L3:
    <pre>=SUM($K$3:K3)</pre>


    Col K will give for each day the number of hours away from home.
    Col L is a running sum of col K.
    Then you can copy these formulas down the rows as desired

    If you are looking for the values (eg 29 and 60) to appear when you come home and have "blanks" in cells when you are away, in addition to the above calcs you can add in N3:
    <pre>=IF(OR(ISBLANK(A3),H3<>M3),"",L3-SUM($N$2:N2))</pre>

    If you copy it down the rows, it will show nulls In rows 3, 5,6, 8-whatever. In row 4 will be 29 and row 7 will be 60.

    Steve

  6. #6
    Lounger
    Join Date
    Nov 2004
    Location
    Oroville, California, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Figuring time (1993)

    Steve, we are on the right path. I have modified the spreadsheet to be more readable. This is what I have come up (see example attached spreadsheet).

    On Duty @ home terminal (cell D3) on the 1st (cell A3) @ 12:00 am (cell E3). Spent time at away terminal. Returned, and off duty @ Home Terminal (cell H4) on the 2nd (cell A4) @ 06:00am (cell I4), for a total time away from Home Terminal of 30 hours (30 hours should be reflected in cell K4. K3 should be blank).

    Second example, went on duty on the 4th (cell A5) @ Home Terminal (cell D5) @ 02:00 am (cell E5). Came back to home terminal (cell H7) on the 6th (cell A7), going off duty @ 2:00 pm (cell I7) for a total time away from home of 60 hours. Cells K5 & K6 should be blank, and Cell K7 should reflect 60. Additionally, I would like cells K8:K12 show up blank till needed, I would also like cells L8:L12 to also show up blank till needed. I do not understand the function of column "N".

    Please let me know if more explanation is needed.

    Thanx

    PSYRR

  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: Figuring time (1993)

    Col N is the value you want in Col K it seems. (you didn't copy it down the rows or you might have seen this).
    In N3 enter:
    <pre>=(IF(H3=M3,A3+I3,A3+1)-IF(D3=M3,A3+E3,A3))*24</pre>

    copy N3 from N4 to the last row of data.[This column could be hidden if desired it is the daily time away from home]

    In L3:
    <pre>=IF(K3="","",SUM($N$3:N3))</pre>

    This will show up blank until a value is in K3.

    If you want a day-by-day sum use in L3:
    <pre>=IF(ISBLANK(A3),"",SUM($N$3:N3))</pre>


    In K3:
    <pre>=IF(OR(ISBLANK(A3),H3<>M3),"",SUM($N$3:N3)-SUM($K$2:K2))</pre>

    Copy K3:L3 down the rows as desired.

    Steve

  8. #8
    Lounger
    Join Date
    Nov 2004
    Location
    Oroville, California, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Figuring time (1993)

    Steve, Thanks. See attached. Appears to be working wonderfully. Can I take cell L3 in the attached and have it show the bottom line (accumulated total) of bottom line in column "O" (currently 90), and have cell K3 take the amount of hours in L3 to reflect the amount of days per 24 hour period. In other words, (currently) 90/24=amount of days/trips.

    The IRS wants us to use each 24 hour period as a trip away from home. Thus the accumulated total at the end of the year in column "O" will be divided by 24 to reflect the amount of trips made for the year. I would like to use Cell K3 to use as a runing total of trips made, and Cell L3 to show the accumulated amount of hours spent away from home.

    Thanx

    PSYRR

  9. #9
    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: Figuring time (1993)

    How about in L3:
    <pre>=MAX(O:O)</pre>


    and in K3:
    <pre>=L3/24</pre>


    Steve

  10. #10
    Lounger
    Join Date
    Nov 2004
    Location
    Oroville, California, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Figuring time (1993)

    thanks. Works perfect,ly. Fellow railroaders will appreciate this.

  11. #11
    Lounger
    Join Date
    Nov 2004
    Location
    Oroville, California, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Figuring time (1993)

    I have discovered that the formula is not ading the time up correctly for going on and off duty at the home Terminal (this case Stockton). Please see the attached and advise.

  12. #12
    Lounger
    Join Date
    Nov 2004
    Location
    Oroville, California, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Figuring time (1993)

    Maybe some clarification is needed here. I weno on duty @ 11:00 pm on 1/28 at Stockton, and off duty @ 3:15 pm on 1/29 @ Keddie. Should i add another column to reflect the off duty date(s), or can the formula be adjusted to automatically reflect if going on duty on day 1 and off duty day 2? This appears to be the problem of not counting the total hours away from home.

  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: Figuring time (1993)

    Try a new approach

    In N3:
    <pre>=IF(H3=M3,24*(MAX($Q$2:Q3)-MAX($P$2:P3)),"")</pre>


    In O3:
    <pre>=IF(N3="","",SUM($N$3:N3))</pre>


    In P3:
    <pre>=IF(D3=M3,A3+E3,"")</pre>


    In Q3:
    <pre>=IF(H3=M3,A3+I3,"")</pre>


    Copy/Autofill N3:Q3 down the rows

    Steve

  14. #14
    Lounger
    Join Date
    Nov 2004
    Location
    Oroville, California, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Figuring time (1993)

    Thanks again Steve. Looks good. Will run it thru it's paces and let you know if i encounter any unforseen problems (none so far, looks good).

  15. #15
    Lounger
    Join Date
    Nov 2004
    Location
    Oroville, California, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Figuring time (1993)

    I have found a flaw. Attached is a copy of my spreadsheet. I went on duty @ 3:30 PM on 3/20, and off duty @ 3:00 AM on 3/22 for a total time of 35 hours and 30 minutes. Computed time indicates 11 hours and 50 minutes away from home. Please help.

Page 1 of 2 12 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
  •