Question
I want to enter 1 start time for the first of a series of sequential events, and the duration of each event in the series. How can I get the program to automatically figure the end time for the first event, and the start and end times for each of the subsequent events?
Answer
Assume that Event Durations are in column A, Start Times in column B, and End Times in Column C. Entries begin with cell A1. Also, each event begins when the previous event ends)
1) Select the first duration cell (A1) and enter the duration for the first event in minutes entered as an integer. (1.5 hours duration would be entered as 90.)
2) Select the first start time cell (B1) and enter the start time (format the cell as TIME using "Number..." from the FORMAT menu)
3) Select the first End Time cell (C1) and enter the following formula in the Entry Bar=TIME(HOUR(C1)+INT((MINUTE(C1)+B1)/60),MOD((MINUTE(C1)+B1),60),SECOND(C1))
4) Hit the enter key to save the formula .
5) Click on cell C1 and drag down as many rows as there are events.
6) Choose FILL DOWN from the CALCULATE menu.
7) Select cell B2 and enter the formula = C1 in the Entry Bar and hit the enter key.
8) Click on cell B2 and drag down to the row for the last event.
9) Choose FILL DOWN from the CALCULATE menu
Now as you add durations for each of your events, start and end times will automatically be calculated for all events.