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:
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 timesto make the tripthe 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 (3rd 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 beforethe 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.