Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Formula help needed

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm an excel dummy, so please bear with me!

    I'm creating a worksheet that tracks how long a certain project takes to get completed. The sheet, too, needs to calculate how long the project spent in different locations.

    Ie:

    I have an initial start time, go to stop two, three, four, etc.

    At each of the stops, I've set up an "elapsed time" field to calculate the time it took to get from one to two and then from two to three, etc. At the end of all stops, I need a formula to calculate total duration.

    I've run into a problem, though. Some projects may need to go two stops before they are completed while other projects might need to go to three, or four, etc. How do I make the formula know that if one of the duration times is blank, that that particular project is completed? How do i let the final total field know that as well?

    (ie for some projects it will need to add stops 1-7 while other projects it only needs to add from maybe 1-2?

    Thanks!
    Dawn

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    Column A = Initial Start?
    Column B = Arrived at Stop 1?
    Column C = Total Time from Start to Stop 1?
    Column D = Arrived at Stop 2?
    Column E = Total Time from Stop 1 to Stop 2?
    ect...
    ect...
    Column I = Last Stop - Initial Start?

    Do you account for the time spent at each individual stop?

    Need more details please...
    That is why you have not recieved any replies.

    Please email a copy of your sheet or post more details.

    Thanks
    TsTom@hotmail.com

    [ This Message was edited by: TsTom on 2002-04-06 11:31 ]

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tstom:

    I want to set up my worksheet exactly the way that you formatted it:

    Column A = Initial Start?
    Column B = Arrived at Stop 1?
    Column C = Total Time from Start to Stop 1?
    Column D = Arrived at Stop 2?
    Column E = Total Time from Stop 1 to Stop 2?
    ect...
    ect...
    Column I = Last Stop - Initial Start?

    How do I formulate it so that Excel knows where my last stop is?

    For instance if I'm tracking 20 projects and they all complete at different stop points--how do i format each "elapsed time" cell to know that each of those cells is potentially the last stop?

    Does that make sense?

    ( and i do not need to account for the time spent at each stop)

    Thanks again!

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Dawn...
    How will this time be displayed?
    In hours/min elapsed?
    Do all of these stops occure in one day?
    A week or more?
    In essence
    Time displayed as Weeks/Days/Hours/Min
    Days/Hours/Minutes
    Hours/Min
    Min

    Which?
    Thanks
    Tom

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tom,

    Sorry that i'm making you pull teeth to try to find out what I'm doing!

    The time will be displayed in hours and minutes elapsed. The projects will all be completed in a 24 hour time frame so I would only need the time to display in hours and minutes

    Thank you!

    Dawn

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,423
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello Dawn

    I used named ranges but that is not essential.

    Project__ Total_Time Start1 Stop1 Start2 Stop2 Start3 Stop3
    Project_1 10:30 7:00 10:00 10:30 12:00 14:00 20:00
    Project_2 8:30 8:00 10:00 10:30 11:00 14:00 20:00
    Project_3 13:00 20:00 1:00 6:00 10:00 16:00 20:00

    Formula if times do NOT span midnight
    =Stop1-Start1+Stop2-Start2+Stop3-Start3

    Formula if times may span midnight
    =(Stop1-Start1+(Start1>Stop1))+(Stop2-Start2+(Start2>Stop2))+(Stop3-Start3+(Start3>Stop3))

    Format results to time

    [ This Message was edited by: Dave Patton on 2002-04-06 13:48 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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