Formula help needed

[dawn f]

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

[Tom Schreiner]

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 ]

[dawn f]

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!

[Tom Schreiner]

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

[dawn f]

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

[Dave Patton]

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 ]