A Timestamp Macro for Task Timing by Tom Tullis Originally posted March 29, 2008

Many usability professionals like to "do their own thing" when it comes to data-logging during a test. They like the freedom of typing a "stream-of-consciousness" transcript of what's happening in the test. Consequently, MS Word is probably one of the most commonly used data-logging tools in usability tests. Both MS Word and Excel have the ability to insert the current data and time into a document or spreadsheet. The problem with Excel is that it always updates those dates and times to the current date and time, even the ones you previously entered. But in Word, you can tell it not to do that. In Word*, if you select the "Insert" > "Date and Time" menu item, you get the following dialog box:

You can choose from a variety of formats for the date and/or time. You do not want to check the "Update automatically" box. This will insert the date/time as text at the current typing position. While you could use this menu and dialog box whenever you want to do this, it's quicker and easier to use a macro. The following macro can be used to insert the date and time:

Sub Timestamp()
   Selection.InsertDateTime DateTimeFormat:="h:mm:ss am/pm", InsertAsField:= _
       False, DateLanguage:=wdEnglishUS, CalendarType:=wdCalendarWestern, _
       InsertAsFullWidth:=False
   Selection.TypeText Text:=vbTab
End Sub

That macro can be entered by selecting "Tools" > "Macro" > "Visual Basic Editor" and then pasting the text above. You can then associate that macro with a keyboard shortcut by selecting "Tools" > "Customize…" > "Keyboard…". Find "Macros" listed in the "Categories" list and then "Timestamp" in the "Macros" list:

Highlight the "Timestamp" macro and then define the shortcut key you want to use. The screenshot above shows it associated with the "Alt+Shift+T" keystroke. (Note that there is a predefined keyboard shortcut, Alt+Shift+T, to insert the current time, but it inserts the time as a field which automatically updates to the current time.)

You can use this macro to insert the date and time whenever you want as you're taking notes during a usability test. Note that the macro as shown above automatically inserts a tab after entering the time. You can use this macro to easily record the start and finish times for tasks, as shown here:

In this example, the headings, "Task", "Start Task", and "End Task" were manually typed, with tabs between them. At the beginning of each line, the task number was typed followed by a tab. Pressing the keyboard shortcut (Alt+Shift+T in the example above) automatically inserted the time and a tab. Pressing the keyboard shortcut again when the participant finished the task automatically inserted the time again. Of course, you could also enter notes if you want:

The advantage of adopting a structure like this for entering the data is that following the session you can easily paste it into Excel for analysis:

It works best to paste the data as text (using "Paste Special..." > "Text"). Then you can easily add a column for the calculation of the elapsed time for each task:

An Excel spreadsheet showing this example is available via that link.

The "Elapsed Time" column is simply the end time minus the start time, with the cell then formatted as "h:mm:ss". The "Elapsed (mins)" column is the "Elapsed Time" * 24 (hours in a day) * 60 (minutes in an hour), with the cell formatted as a number to one decimal place. You could have converted to seconds by multiplying by another 60 (seconds in a minute).

* This article, including its screenshots, is based on Microsoft Word 2000.