Excel’s date string parsing to fix dates & time for SPSS
SPSS only reads strict date & time input formats, but surveying applications don’t always output dates that way. Qualtrics, for example, outputs dates in the following way - “2009-11-03 16:39:47” which can then only be used as a string. To make use of that we need to do some date conversion.
In the following example, I had the Qualtrics date/time for the date+time the participant began to take the survey as well as the time the participant finished the survey. The goal was to get the number of minutes it took the participant to finish.
To do that it’s best to use Excel’s string parsing. Take a look at the following example where C2 and D2 hold the start and finish time for the second participant. Since Qualtrics outputs date and time in the same box we’ll separate those into different boxes for date and time. E2 and F2 parse the date (C2 and D2 respectively), while G2 and H2 hold the time (same). Excel then makes it very easy to calculate the time difference between the two time boxes.
So, essentially, it’s about using the functions left(), mid(), & right() to parse the text.
To make it appear the right way, the parsed boxes should be set to the right date/time display (right-click->format). Another simple example can be found in “Convert Dates To Excel Formatted Dates”. Once done with Excel, copy paste the results into SPSS and you’re done.
(post written as a guide for a colleague who was struggling with this)