Hi Rick
Thanks for this. My problem is that I created a new exam format, and I'm anticipating that some Administrator may query whether or not I allowed enough time for the exam. Now, I can answer what I observed, that all but one of the students completed the exam before time, that many students completed well before time, and that the one student that ran full time took more than 20 minutes to answer the last three questions (MCQs), just going back and forth reading and re-reading the questions until she finally had to settle on an answer before running out of time. (I think if I had given that last student an extra hour, she would have used it as well, hoping for some extra insight).
That description is enough to assure most educators that the time allowed was enough. Unfortunately, that answer cannot be reduced to a single number. It helps more if I can show that I allowed 2 hours for the exam, 193 students took the exam, and the average time was 1 hour and 4 minutes. (If necessary, with other stats also).
In answer to your solution - it sort of worked, but only really works if all candidates took more than 1 hour. If some took less than an hour, then the display is in minutes and seconds, and so the split across the columns is different.
But your solution gave me another idea: I used the Text to columns to split the Date and time of the Started and Completed times, and then saw that the separated times were in Excel's time format, and I could subtract one from the other. Then I figured, well, if the separated times are in Excel format, then maybe the original Date/time is also in proper Excel format, and, if so, I can simply subtract the Start from the Completed. And it works. It give the answer in days, so a simple multiplication by 24 and 60 (=((SUM(I2-H2)*24)*60)) gives the time in minutes, and that is a simple number, so I can find the mean of that.
Thanks very much.
Regards