Keep Track with T.E.C.L.

This is an idea that I found useful when writing a short story for a competition.

Background

I started on the story last year but it all turned out a bit too rushed, so I put it to one side, giving the rapidly-looming deadline a miss. I dug it out again recently, with a view to submitting it this year instead.

Entries can be up to 30.000 words long, so very much towards the upper end of what I’d call a short story, although mine should end up being around half that length. Still quite long, I feel. It’s set in a partly fictional, partly real-world backdrop over a 70-year time frame, with a fair bit of time-hopping thrown in. All of the characters were born at different points during that 70-year period.

The Difficulties

And therein lie the issues that I had. I’m more used to briefer narratives, with fewer central characters, set over a shorter period of time. I didn’t know it when I started but keeping a clear overview of exactly what was happening when and to whom was going to get tricky…

As it evolved, the ages of the characters at certain points in the story became clearer, which was fine because everything was still relative to a fairly arbitrary start date. The problem came when I started introducing several real, historical events into the story.

Lengthy to explain in detail without going into the bare bones of the piece but I found myself having to keep changing dates and ages of several characters, sometimes with the help of a calculator, in order to get things to work properly. Before realising that something else down the line was now broken because another character would then be of a slightly unsuitable age for that particular scene. Some of my ideas just didn’t work together in practice.

Trouble is, real-world dates from the past are of course immovable, set in stone. Much of the whole premise for the story wouldn’t work without them so I couldn’t just leave them out or use something else instead.

Planning

Plan it, you say, get a grip. Plot out your story arc first. Yes, that’s exactly what I wanted to do, I was just missing some kind of ‘helicopter view’ of things. That’s when I came up with the little tool described below. It gave me an overview of the ages of all my characters at every important point in the story, letting me easily tweak their birth dates to see at a glance whether or not everything else would still fit.

I don’t often plan fiction in much detail, because I find that the best ideas tend to emerge from the very act of writing, fleshing out much of the important stuff as you go along. I will have a rough idea of where everything is heading but I usually find that the real ‘meat’ reveals itself along the way. With all the inevitable backtracking and rework that this entails, this kind of approach does feel inefficient at times but it’s how I usually end up doing things.

Still, the overall piece needs to make sense in the end because people will soon notice if it doesn’t – why jeopardise things by getting basic stuff wrong just because of insufficient attention to a few dates?

Using a T.E.C.L.

For want of a better name, I called this tool the Time-Event-Character List. I tried calling it T.I.C.L. to make it more memorable but e.g. ‘Incident’ instead of ‘Event’ just didn’t sound right. It’s not something I’ve seen described before, so ‘tis mine, as it were, although I’d be interested in hearing from people who’ve used similar approaches.

It felt like a natural way to address the issues I was having and many will agree that as fixes go, it’s nothing special. So why bother posting about it here? I guess I’m thinking that this kind of solution might be less straightforward for those who don’t tend to use spreadsheets – if that’s the case then this post is for you!

All you need is Microsoft Excel. The basic idea will work with other types of spreadsheet although the functions I’m using most likely won’t be fully interchangeable, unless that flavour of spreadsheet is designed to be compatible. I’ve also included a slightly different version that will work with Google Sheets which, unlike Excel, is free of charge.

For the example below, I’ve chosen a few members of the British Royal Family, listing some important events from their lifetimes down the left-hand side. Imagine those events as being key scenes or happenings in your own fictional work, e.g. a chance encounter with an old friend, a school-girl crush, a heinous crime, a family celebration – anything at all.

The other important part is the top row: each character (i.e. royal family member) in the story has a column entry in which you specify when they were born and when they died, the latter simply being left blank if that person is still alive at the end of your story’s overall time frame.

Pop in all these details and hey presto, the entire sheet gets updated with each person’s age for each listed event, letting you experiment with changing birth and/or death dates to see if everything still works out, age-wise.

The display looks like this:

and here zoomed in, to show the details more clearly on smaller screens:

For each event in column B, you see when it happened, together with the age of all the characters at the time, assuming that they live(d) to celebrate a birthday that year. The ages following somebody’s death show up as negative numbers, i.e. the number of years since they died. Meaning that key death-anniversaries are also easy to spot, something that I also needed to have ready-to-hand for my own story.

Each character’s entry has the format:

name:yyyy-yyyy

where the first ‘yyyy’ is that person’s year of birth and the second is their (optional) year of death:

Here’s how to construct such a spreadsheet from scratch, it only takes a few minutes:

Cookbook for Building a T.E.C.L

Start off with a blank worksheet and add to it as follows:

1. First a couple of headings: Type in ‘Year’ in cell A1 and ‘Events’ in cell B1.
2. Enter the starting year of your story in cell A2, e.g. ‘1895’
3. Increment this value for consecutive rows in column A until you reach the final year that you want, so in A3 you would enter ‘=A2+1’, then simply copy A3 to successive cells in column A until the end date is reached.
4. Add each individual character from your story to a separate cell on row 1, starting at C1. For each person, enter their name, their year of birth and optionally, their year of death, delimited by ‘:’ and ‘-‘ respectively. Whilst the year of death can be left out, both delimiters (‘:’ and ‘-‘) must always be included. See the screenshots for examples. The name can be anything, including spaces, but there should be no spaces in the dates, which also need to be precisely four digits long; pad them out with leading zeroes if need be.
5. Once all your story’s characters have been included, select and copy the generic formula shown below then paste it into cell C2, making sure that you include everything from the initial ‘equals’ sign to the final four closing brackets. You’ll probably need to match the destination formatting when pasting: you can simply click on C2 and paste directly into the formula bar at the top before pressing ENTER in order to do this.
6. Finally, copy cell C2 to all cells in your grid, i.e. to the area bounded by the story’s characters along the top and by the years down the left-hand side. For example, if you entered five separate characters (occupying range C1:G1) and twenty-five years in the left-most column (range A2:A26) then you would copy C2 and bulk-paste it to every cell in the range C2:G26.
7. You can now add the events making up your story in column B as free-form text, anything you like.

Additional Notes:

• I usually freeze the top row and left-most column(s) in place (via the View menu) for better readability when scrolling through larger sheets.
• I’ve left the example display plain and simple without any bells and whistles. Applying one of the multi-coloured table formats available in Excel makes it easier for the eye to follow any given range of values so might be helpful.
• If you want to add additional people, or change the start date or the range of years being covered, copy cell C2 to any new cells that you add to the grid. If you do change the start date, already-existing events in column B might also have to be moved.
• You may need to modify the formula for foreign-language versions of Excel which, for whatever reasons (a big pet hate of mine, actually), most likely use completely different names for the functions, as well as e.g. semicolons instead of commas to separate their parameters.
• Negative/B.C. years won’t work as things stand.

Here’s the formula to paste into the spreadsheet:

=IF(NUMBERVALUE(MID(C$1,FIND(“-“,C$1)+1,4)) <> 0,IF($A2 > NUMBERVALUE(MID(C$1,FIND(“-“,C$1)+1,4)), NUMBERVALUE(MID(C$1,FIND(“-“,C$1)+1,4)) – $A2,IF($A2 < NUMBERVALUE(MID(C$1,FIND(“:”,C$1)+1,4)),””,$A2 – NUMBERVALUE(MID(C$1,FIND(“:”,C$1)+1,4)))),IF($A2 < NUMBERVALUE(MID(C$1,FIND(“:”,C$1)+1,4)),””,$A2 – NUMBERVALUE(MID(C$1,FIND(“:”,C$1)+1,4))))

GENERIC CELL CONTENT FOR PASTING INTO CELL C2
(FOR ENGLISH-LANGUAGE VERSION OF EXCEL)

This next version of the formula is for Google Sheets, the free-of-charge spreadsheet available on Google Drive if you have a @gmail account. It’s basically the same as Excel except that it uses Google’s own version of the NUMBERVALUE() function. Which I could only get to fully work by subtracting zero from the result when converting strings to numbers for some reason. But you don’t need to worry about that, just copy the formula below as-is and it should work in the same way as for Excel.

=IF(TO_PURE_NUMBER(MID(C$1,FIND(“-“,C$1)+1,4)-0) <> 0,IF($A2 > TO_PURE_NUMBER(MID(C$1,FIND(“-“,C$1)+1,4)-0), TO_PURE_NUMBER(MID(C$1,FIND(“-“,C$1)+1,4)-0) – $A2,IF($A2 < TO_PURE_NUMBER(MID(C$1,FIND(“:”,C$1)+1,4)-0),”xxx”,$A2 – TO_PURE_NUMBER(MID(C$1,FIND(“:”,C$1)+1,4)-0))),IF($A2 < TO_PURE_NUMBER(MID(C$1,FIND(“:”,C$1)+1,4)-0),””,$A2 – TO_PURE_NUMBER(MID(C$1,FIND(“:”,C$1)+1,4)-0)))

GENERIC CELL CONTENT FOR PASTING INTO CELL C2
(FOR GOOGLE SHEETS)

That’s it. It’d be great to hear from you if you find this helpful when planning your writing!