Interactive Time Lines

I was trying to estimate the date/time for undated events in an 18th century travel diary. Realizing it would help to use OOCalc for date time math such as daily totals and speed estimates, I set up a spreadsheet for the purpose. As I worked on the spreadsheet I thought it would be great to see a time line graph to check my work for errors. Searching around it became clear that none of the built-in graphing functions in spreadsheets would work for creating a time line graphic. It appears that most people simply use drawing tools to make time lines.

Then I found the Simile Widgets Timeline component a versatile JavaScript based solution. Soon after I got a time line going it became clear that keeping the data file in sync with the changing spreadsheet data was cumbersome. With all the data already in OOCalc I decided it would be nice to output formatted data directly from the spreadsheet for use in the timeline.

With my success in creating a spreadsheet for this one project I decided to create a generic version of the spreadsheet that covers all time line attributes. The spreadsheet contains a final version of my custom Calc functions for time line JSON data creation that handles all time line data options (download the spreadsheet here). To test out my new OOCalc functions I created a time line of English and British Monarchs (also useful for my research) starting with my spreadsheet sample. I haven’t finished adding text excerpts to the monarchs time line but it does have pictures and Wikipedia links for all items.

Last month while organizing notes from a tour of historic sites I found I had not recorded the dates and times in the notes. As I was giving myself a dope slap for failing to record the times it dawned on me that the photos I took would give me the missing time information. While I was viewing the photos to get the times from the EXIF data it occurred to me that I could set up nearly automatic time line generation using my spreadsheet and the command line ExifTool.

To start you open a command prompt in the directory with your photos and then run the following command line.

"C:Program FilesEXIFtoolexiftool.exe" -p "$filename, $createdate" -q -f -d "%Y/%m/%d %H:%M:%S" . >PhotoTimes.csv

This creates a CSV data file containing the image filename and creation date that can be opened by OOCalc. Using the spreadsheet you generate a JSON data file to give you a time line as shown in these screenshots.



I’ve put a zip archive with the files used to create the photo time line on my site. Download the archive from this link.

The archive contains these files for the Photographs Time Line:

PhotoTimes.csv output from the ExifTool run
PhotoTimeline.ods spreadsheet for creating the JSON data file
PhotoTimes.js the JSON data file
PhotoTimes.html HTML page for displaying the time line
PhotoTimes.css CSS for better control of image size

See Also:

Simile Widgets Timeline documentation

OpenOffice Calc Tips

It’s been two years since I switched from MS Office to OpenOffice for all my word processing, spreadsheet, presentation and local database needs. I had been using OpenOffice for all my personal documents for a number of years prior and with the advances in Calc’s functionality I was pretty sure it could handle my professional work as well.

After two whole years I can confidently say that OpenOffice Calc does everything I need as well or better than MS Office. Over this period, due to changed business needs, I’ve had to spend more time with spreadsheets than in the previous ten years. With the much heavier usage I was concerned that I would eventually hit a need for some feature of Excel that is not yet implemented in Calc. To my delight I have not found any missing features and my employer got a productivity bonus because I haven’t had to spend time learning the new Microsoft UI.

Lately I’ve gotten around to writing custom functions for Calc and had quite a bit to learn because this is the one area where decades of 123 and Excel experience is not much help. I’ve put some tips and custom functions in a Function Demos Spreadsheet that you can download. One spreadsheet feature that I’ve found to be essential for long term maintenance, that most people don’t use, is named ranges. If you haven’t used named ranges in Calc spreadsheets I recommend reading the tips linked at the end of this post.

Recently I needed to work with a Calc document I had constructed a year and a half ago. While I had used named ranges extensively making it easier to read the formulas, I wanted to change the range names as the abbreviations I had used weren’t as clear as I’d like. With dozens of named ranges defined across multiple sheets and each used on other sheets it wasn’t going to be easy to edit them one at a time. to manually edit a couple range names all you have to do is:

  1. Go to the menu item Insert->Names->Define .
  2. Select the Name you want to change.
  3. Copy the range definition from the Assigned to text box.
  4. In the name text box change the name.
  5. Paste the range definition in to the Assigned to text box.
  6. Then click the add button, you now have two names for the range, click OK to close the dialog.
  7. Use Edit->Find & Replace to change the old name in formulas to the new name.
  8. Go to the menu item Insert->Names->Define .
  9. Select the old name and click the delete button.

While this process is workable for changing a few names, it’s faster to do than to describe in text, it is not really efficient when you’ve got dozens of names to change. To aid with managing the multiple changes I created a custom array function that creates a list of all named ranges, it’s in the Function Demos Spreadsheet. After entering the array function in a new sheet, select the whole array and use the Data->Text to Columns… function to make the name list static. Next copy the name list to the column to the right, and edit that column to the new names you want to use. You now have a neat two column list of old and new range names that you can use to copy and paste names from for the Insert->Names->Define and Edit->Find & Replace dialogs.

General Info

Range Names Tips

Other Tips