Sawtooth Software: The Survey Software of Choice

Converting Respondent Data Timestamps

I'm using SSI Web 8. After I download the data from the Admin Module, and examine the .csv file, the date and time seemed to be stored in a long number. These variables look correct in the Admin module and within SSI Web, but not Excel. How come?

The sys_StartTime value represents the time the survey was started. The sys_EndTime value represents the time the survey was ended. The sys_ElapsedTime value represents the total number of seconds the person took to complete the survey.

These variables, created by a survey's server and stored along with a respondent's answers, are special timestamps that represent the number of seconds that have elapsed since January 1, 1970 (midnight UTC/GMT), not counting leap seconds (in ISO 8601: 1970-01-01T00:00:00Z). This timestamp is also known as the Unix epoch, Unix time, POSIX time, or Unix timestamp. We use this number so that if a survey was simultaneously started by individuals in New York City, and Munich, and Lagos, the same number will be stored, regardless of the respondent's time zone.

If you are examining the data in Microsoft Excel®, here is a handy formula to convert this code to a date and time value set to Greenwich Mean Time (GMT/UTC): =(A1 / 86400) + 25569, where A1 is the cell containing the timestamp. Don't forget to format the cell to display the date/time in the style you desire.

If you want to go the other way and convert a date/time stamp to Unix/epoch, then the formula is =(A1-DATE(1970,1,1))*86400, where A1 is the Unix/epoch time code.

For more information about this timestamp format:

Sawtooth Software

6:30 AM to 5:30 PM Mountain Time
(GMT-6; GMT-7 Autumn/Winter)
Monday through Friday

Phone: +1 801 477 4700
Fax: +1 801 337 7410

SKIM Software

9:00 AM to 5:30 PM Central European
(GMT+2; GMT+1 Autumn/Winter)
Monday through Friday

Phone: +31 10 282 3500
Fax: +31 10 282 3560

Lighthouse Studio

Lighthouse Studio is our flagship software for producing and analyzing online and offline surveys. It contains modules for general interviewing, choice-based conjoint, adaptive choice-based conjoint, adaptive choice analysis, choice-value analysis, and maxdiff exercises.

Try Lighthouse Studio

  Buy Lighthouse Studio