Formula help needed

dawn f · Apr 6th, 2002, 12:05 PM

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 · Apr 6th, 2002, 12:30 PM

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 · Apr 6th, 2002, 12:54 PM

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 · Apr 6th, 2002, 01:30 PM

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 · Apr 6th, 2002, 02:24 PM

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 · Apr 6th, 2002, 02:45 PM

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 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 6th, 2002, 12:05 PM	#1
dawn f New Member Join Date: Apr 2002 Posts: 4	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

Apr 6th, 2002, 12:30 PM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	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 ]

Apr 6th, 2002, 12:54 PM	#3
dawn f New Member Join Date: Apr 2002 Posts: 4	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!

Apr 6th, 2002, 01:30 PM	#4
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	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

Apr 6th, 2002, 02:24 PM	#5
dawn f New Member Join Date: Apr 2002 Posts: 4	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

Apr 6th, 2002, 02:45 PM	#6
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	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 ]