Schedule Worksheet

[Workbook]

In cell B2 I want to display a time. In cell D2 I want to display a time
that is always 30 minutes later than the time displayed in cell B2. I want
cell E2 to display a time that is always 30 minutes later than the time
displayed in cell D2. I want cell F2 to display a time that is always 15
minutes later than the time displayed in cell D2, and I want cell H2 to
display a time that is always 12 minutes later than the time displayed in
cell F2.

Each cell represents an activity therefore; B2 = Activity 1, D2 = Activity
2, E2 = Activity 3, F2 = Activity 4, and H2 = Activity 5. I want to link a
bar graph to all the activities so that each activity has a column attached
to it. As a result if I enter 5:30am in cell B2, I can see a column that
indicates what time activity B2 will begin, end, and how many minutes it will
take. I can see a column that indicates what time activity D2 will begin,
end, and how many minutes it will take. I can see a column that indicates
what time activity E2 will begin, end, and how many minutes it will take. I
can see a column that indicates what time activity F2 will begin, end, and
how many minutes it will take, and so on.

Can you help me set up a Worksheet that can perform these functions using
Excel 2003?

 

[Pete_UK]

So what are you using columns C and G for, and how will these affect
the graph? How would the graph know the duration of each activity?

I would suggest you enter the duration (in minutes) in, say, row 3, so
that you would have these formulae in the cells stated:

D2: =IF(B2=0,"",B2+B3/60/24)
E2: =IF(D2=0,"",D2+D3/60/24)
F2: =IF(E2=0,"",E2+E3/60/24)
H2: =IF(F2=0,"",F2+F3/60/24)

and you would have these values below:

B3: 30
D3: 30
E3: 15
F3: 12
H3: 10

I have assumed that Activity 5 will take 10 minutes.

Then you can enter your time in B2 and the other cells will
automatically adjust to show the appropriate starting time, though you
will need to format the cells on row 2 to show the times in the style
you prefer.

Hope this helps.

Pete

 

[Workbook]

Hi Pete,

Thank you for your feedback. You have some great questions that helped me
to clarify what I am trying to communicate. Picture this. Let’s say Cells
A1, C1, and B2 make up Activity 1. Cell A1 displays the time that Activity 1
begins, Cell C1 displays the time Activity 1 ends, and Cell B2 displays the
time difference between Cell A1 and C1. So, if 5:30am is the time that I
decide Activity 1 begins. Cell C1 and B2 are linked to cell A1 so that
whatever time I enter into cell A1, Cell C1 will display a time that is 30
minutes later, and Cell B2 will display the time difference between Cell A1
and Cell C1. For example If Cell A1 displays the time 5:30am then C1 would
display the time 6:00am, and Cell B2 would display 30 minutes. So I am
trying to figure out what formula do I need for cells C1 and B2? and what
format do those cells need to be in so that the formula will work?


The second thing I am trying to do is figure out how to set up the data
range, titles, and axes in the chart wizard so that I can see on a Bar graph
what time activity 1 begins, ends, and how long it takes. These are the
things I am looking for help with? Does that answer you questions?

Thank you,

Ed

 

[Pete_UK]

You want the data for your graph to be contiguous. If you highlight
B3:H3 and then click on the Chart Wizard, you will include C3 and G3,
and you probably want to avoid this.

The suggestions I gave you were to use row 2 for the times and row3
for the durations (in minutes), but I'm not sure how you want to
display this on a graph.

Would a Gantt chart be more suitable for your display? If so, Jon
Peltier shows how you can construct one here:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=343

and there are links to other sites about Gantt charts here:

http://peltiertech.com/Excel/Charts/GanttLinks.html

You might want to arrange your data slightly differently.

Hope this helps.

Pete

 

[Workbook]

Hi Pete,

It helps very much this is awesome! Good advice, I got rid of any empty
columns and created a table. I understand your suggestion now for the times
and durations. The links you provided are excellent by the way. Yes, a
Gantt chart would be most suitable.

Yes, I am having trouble with, displaying the data on a graph. Use the data
range in the Start Column and End Column, Insert Chart, Bar, Stacked Bar, but
the time ranges from 12:00am to 4:48pm. I just want the time ranges to go
from the start of Activity 1 till the end of Activity 7. Do you have any
thoughts about how I might make that happen?

Activities Start End Duration
Activity1 5:30 AM 6:00 AM 30
Activity 2 6:00 AM 6:30 AM 30
Activity 3 6:30 AM 6:34 AM 4
Activity 4 6:34 AM 6:44 AM 5
Activity 5 6:39 AM 6:44 AM 5
Activity 6 6:44 AM 6:56 AM 12
Activity 7 6:56 AM 7:11 AM 15

Thank you,

Ed

 

[Pete_UK]

If you position the mouse pointer over an axis and then right-click,
you will be presented with some options, including Format Axis. With
this you can change Excel's default settings, which will be to
autoscale the axes etc. Just play about with different settings to see
if you can find one more suitable.

Hope this helps.

Pete

 

[Workbook]

Thank you Pete, you are the man!

If you position the mouse pointer over an axis and then right-click,
you will be presented with some options, including Format Axis. With
this you can change Excel's default settings, which will be to
autoscale the axes etc. Just play about with different settings to see
if you can find one more suitable.

Hope this helps.

Pete

 

[Pete_UK]

Well, that's nice to hear, just before bedtime - thanks for feeding
back.

Pete