I am constructing a bus timetable.

I am starting with a base time of 9am (Cell B2), with irregular intervals:

    B         C         D
2  09:00    10:30     12:00
3  09:05
4  09:12
5  10:14
6  10:20

When the bus comes back to the depot it will depart again at 10:30 (Cell C2), and 12:00 (Cell D2) the third time.

Is there a formula so that the time intervals given in rows B2:B6, can be mimicked in rows C3:C6, and D3:D6?

I can PM image if needed!


Type in C3:

= $B3 - $B2 + C2

$B3 - $B2 gets the time interval from 1 stop to the other, and + C2 is to add this time interval to C2.

Copy this formula to C3:D6 to complete the table.


On C3 put =$C$2+(B3-$B$2) and copy and paste the formula to all the cells you need on columns C and D.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.