Timer for timed exams (more than a simple countdown timer)

[dsengupt]

Hi All,

I’m looking to build a timer for taking practice exams with two functions: an “overall count down timer” and a “problem timer.” I can think of two ways of this potentially working – the first is sort of complex (at least for me) and the second is a little bit simpler (but still beyond my excel knowledge). I’d appreciate your thoughts and help on either.

Thanks!
Dev

Approach 1 : Complex

In terms of layout, I would like to have the “Overall test countdown timer” at the top of the worksheet and input cell for the number of total problems. Column ‘A’ would have in its rows the problem numbers (e.g. 1, 2, 3…), in Column ‘B’ the student would enter his answer to each question (e.g. A, B, C, D or E) and then in Column ‘C’ the time it took the student to complete the problem would be recorded. There would also be one button called “Begin” to start the test timing.

The overall countdown timer would function something like Tushar Mehta’s timer (the user enters the total exam length in minutes) and the timer counts down (in minutes: seconds format).

The "problem timer" timer counts (and then records) the number of seconds it takes a student to complete a problem. It would be activated first when the test begins, and then would be re-set each time the student enters an answer in the appropriate cell in ‘Column B’.

Process
1. Student enters length of exam and number of problems
2. Worksheet automatically numbers rows in Column ‘A’ from ‘1’ to the total number of problems
3. Student clicks on “Begin”
4. Overall countdown clock begins ticking down and the problem timer clock begins counting (problem clock can tick away in the background, does not need to be seen by the student)
5. Student enters answer choice into Column ‘B’
6. Problem timer clock records the amount of time it took to do the problem and then resets.
7. Process continues until the end of the specified time is reached.

Approach 2: Simpler

Alternatively, a simpler approach might just have the following functionality:
1. Column A with problem numbers (1,2,3 …etc, as above)
2. Column B would be empty until answer choices are entered
3. Each cell in Column C would just record the time at which the cell immediately to itself changed from blank to non-blank (i.e. when something was entered into the corresponding cell in Column B)
4. After the exam the user could run a few simple calculations to get to the time it took to finish each question.

Any ideas on how to capture the time that something is entered into a cell (i.e. how to implement step 3)

Thanks everyone!
Dev

 

[tactps]

This assumes a start time when the workbook is first opened:

1. Press Alt-F11 to open VBA
2. Double-click on "ThisWorkboook" in the left pane
3. Paste this into the right pane:

Private Sub Workbook_Open()
[d1] = Format(Now(), "hh:mm:ss")
End Sub

4. Double-click on the sheet name that you want to add the times to.
5. Paste this into the right pane:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target.Row > 1 Then
Range("D" & Target.Row) = Format(Now(), "hh:mm:ss")
Range("E" & Target.Row) = Format(Range("D" & Target.Row) - Range("D" & Target.Offset(-1, 0).Row))
End If
End Sub

The only issue with this is that it requires each answer to be input in succession (i.e you can't answer question 4 before question 2)

 

[tusharm]

Either one is doable; neither is particularly more or less difficult than the other. You will have to use programmatic support update the various cells in column C. I suspect it will be more of a challenge devising a protocol to handle the case where someone goes back and changes an answer (or, more generally, answers questions in a non-sequential manner).

Basically, all you need for figuring out when to update a cell in column C is the worksheet_change event (or for a properly designed add-in the equivalent application event).