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.
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|