I mentioned
earlier this month that I created a spreadsheet to manage the
timeline for November 2018’s NaNovel. Most of it was along the
lines of “this event happens so-and-so minutes after the previous
event” but there were also

- events that had to happen
*before*other events. - events that had to happen relative to much earlier events.

It also involved a
starship accelerating constantly at a multiple of 1g, the
acceleration due to gravity, (which is a clue that the Galactic
Imperium sneers at our primitive view of the laws of physics). I had
to keep track of how far away from Earth the ship was while heading
for a hyperjump point in the outer solar system, to take into account
lightspeed delays in communication. This post is for those who love
spreadsheet hacking; I’ll say a few words about how I did all this.

(A-Z challenge N logo)

I’ve
made a viewable
Google Sheet with a partial timeline for the story, so
if you grok multiple sheets and formulas, you can follow along.

The basic
technological decisions I made about this universe were:

- There are jump points in the outer reaches of many solar systems where ships can instantly jump from one system to another, like with the Alderson Drive in Jerry Pournelle’s Codominium / Empire of Man universe. You have to be nearly at rest with regard to the jump point in order to use it.
- Ships can travel at a moderate multiple of 1g, with “inertial compensators
^{*}” keeping the acceleration felt by passengers down to something humanly tolerable. Or better, tolerable to the aliens from the lowest-g world travelling on a particular ship. At the midpoint of a trip you stop accelerating, flip the ship around (“turnover”), and start decelerating. - There is no faster-than-light communication other than taking a jump.

The
first thing I needed was to
calculate how long it would take to get from Earth to the jump point
at various possible accelerations, because I wanted the
journey to take somewhere within a
particular range of times
to make the trip
the right duration to
fit in the set of events that had to happen before the jump. This
led to the “Jump 1” spreadsheet. It
turned out that 17g gave about the amount of time I wanted, putting
both turnover and jump in the nighttime when passengers could be
strapped to their beds (since turnover is a brief period of zero g,
and jumps are distressing for Reasons). This
particular acceleration has the ship reach about 10% of the speed of
light at turnover (column D of the Jump 1 spreadsheet). “Jump 2”
is a similar calculation for the trip from one jump point in the
second stellar system to another; ships have to make a series of
jumps to go long distances.

In the spreadsheet,
white cells are open to editing; yellow cells are calculated from
other information. The first five columns of the spreadsheet are

- A name for the event: a phrase long enough to remind me of what scene this is part of.
- The (calculated) time the event takes place, since the start of the first day of travel, in hours:minutes:seconds.
- The time difference of this event
*either*since the previous event,*or*, if the fourth column names some other event, since that other event. - Normally blank, but may name some other event to which this one is relative.
- An intermediate calculation showing what time the referenced event (previous or named) took place.

Most of the time
difference entries (3

^{rd}column) are “normal” numbers – 10 minutes since this, 8 hours since that – but a few are references to other calculations. Row 24 references the calculation of time to turnover after departure; row 33 references the same number, this time for deceleration from turnover to the jump point.
Row 27 shows a
character being attacked

*before*the event in the next row (start of dinner). The lookup function in column E doesn’t care where it find the event you’re referencing; it can be anywhere in column A.
Column G is time of
day; the only difference from column B is the formatting code. Column
B uses total hours ([HH] date formatting code); column G ignores
multiples of 24 hours (HH date formatting code).

Columns H-K are
calculations of how far the ship has gone, in Astronomical Units (the
approximate distance from the Earth to the Sun), the velocity in
metres per second, and the lightspeed delay (lag) between the ship and Earth. The calculations
are based on Newtonian physics and don’t actually take relativistic
effects into account; they’re accurate only for low time dilation.
Someday I may replace them with the correct ones, but I decided I
could get away with an approximation for NaNoWriMo.

The lightspeed
delay gets used in one particular place. Row 29 says “Ask Earth;”
this is where the protagonists need critical information that isn’t
in the ship’s computers, and have to wait for their query to get to
Earth, for the people there to research the answer, and then for the
reply to reach the ship. Row 32 has “receive info from Earth.”
The row 32 time difference column involves three intervals, two of
which approximate lightspeed delays from the ship to Earth and back,
and one (1.5 hours) is the amount of time the people on Earth take to
provide the information. I took the two-way lightspeed delay
over the whole time as the sum of the delay when the message was sent
and the delay when the message was received. However I couldn’t
make like 32 depend on itself, so I added row 31, “Just before
receive,” and kept adjusting its time difference until it was
indeed just before the receive time. This took several attempts,
because each time I increased row 31’s difference, it advanced
row 32’s a little bit.

I am convinced that
some algebra could have avoided this complication, but at
the time I created the spreadsheet I wasn’t up to it. As we
educators say, “the exact formula is left as an exercise for the
reader.”

Columns A-E would be
useful in other sorts of stories, if I ever write another where
timing of events is critical. For most of my stories, tightly
correlated time intervals aren’t an issue, and complex spreadsheets
aren’t necessary.

*Apologies
for linking to the black hole that is TV Tropes.

## No comments:

## Post a Comment