Automatic adjustable time formula

[bizco101]

Hello,
I am trying to create an agenda for an upcoming conference and as I know there will be numerous changes made to the agenda between now and the conference start, I would like to create a formula that automatically adjusts any changes in the timing of events, rather than enter each change manually.

The agenda is laid out similar to below. So ideally if the times designated for specific discussions areas follows:

8:00-8:15 bob smith speaks
8:15-9:20 Jane Smith speaks
9:20 -10:15 Break

I would like the formula to automatically adjust all the entries so that if I change Bob Smith's speaking time to between 8:00 and 8:25, all the other entries will adjust accordingly.
Any insight would be truly appreciated

 

[njimack]

Welcome to the board. I'd suggest using another column (which can be hidden or out of sight that contains the length of each speaker. Something like this (the shaded cells are manually entered):

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=192 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Start</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Finish</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Length</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffff99" align=right height=17 x:num="0.33333333333333331">08:00</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="0.34375" x:fmla="=A2+C2">08:15</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ffff99" align=right x:num="1.0416666666666685E-2">00:15</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="0.34375" x:fmla="=B2">08:15</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="0.3888888888888889" x:fmla="=A3+C3">09:20</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ffff99" align=right x:num="4.5138888888888888E-2">01:05</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="0.3888888888888889" x:fmla="=B3">09:20</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num="0.42708333333333331" x:fmla="=A4+C4">10:15</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ffff99" align=right x:num="3.8194444444444441E-2">00:55</TD></TR></TBODY></TABLE>

B2: =A2+C2
A3: =B2

 

[bizco101]

Wow,
That was fast....I'm very new to the whole excel thing, in terms of formulas, so is what you've provided the whole formula. I apologize for the required spoon-feeding!
Thanks,
B.

 

[njimack]

Yes, just enter the formulae exactly as I have posted them, then copy them down as far as necessary.

Changing the cells in yellow will cause all the formulae to recalculate.

 

[bizco101]

Any tips on formatting the cells in the third column. I tried a similar formula yesterday, but the actual time kept showing up in the the third column, rather than the elapsed time. FOr example, if I entered 0:15, it would show up as 12:15...