Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Please help me ruin my son's vacation! (I'm making him study math).

He has to do a huge amount of Geometry exercises, so I was thinking I'd use Excel to track progress: For each question he does I'd use two cells: One to track the time he's started working on the question, and the other to store the duration it took him to work on the question. This way I'd be able to track his progress and I'd also be able to do a little social engineering by proving to his teacher that he really invested some effort.

Now, let's say we have cells A1, B1 and C1 dedicated to the first question. My idea is that when he starts working on the the question he'll enter the current time to the cell A1. When he stops working on the question he'll enter the current time to cell B1. C1 will then calculate and store the duration by subtracting A1 from B1. (Version 2 would maybe host a button in cell so that clicking it once would fill A1, clicking it again would store the value in B1 and calculate C1 :))

However, if I do A1=NOW() the time is not a value but a formula that is "constantly updated". What I want is to do A1=the returned value of NOW at the time my son started processing the question.

How do I do this?

share|improve this question
    
@Lance - Thanks! –  Avi Apr 14 '11 at 22:00

2 Answers 2

up vote 2 down vote accepted

Ctrl + ; will insert the current date into a cell as a fixed value. Combine with Shift to get the current time.

Source: http://office.microsoft.com/en-us/excel-help/insert-the-current-date-and-time-in-a-cell-HP003056107.aspx#BMinsert_a_static_date_or_time

Note:

This is for a standard US keyboard layout. On other keyboard layouts, the combination may be different; for example with a standard German keyboard layout it's . instead of ;.

share|improve this answer

I like the idea with a button better, so here is some code you can use with that:

Global i As Integer
Sub Button1_Click()
ActiveSheet.Shapes(1).Select
If Selection.Characters.Text = "Start" Then
    i = 1
    Do Until IsEmpty(Range("A" & i).Value)
        i = i + 1
    Loop
    Range("A" & i).Value = Now()
    ActiveSheet.Shapes(1).Select
    Selection.Characters.Text = "Stop"
    Range("A" & i).Select
ElseIf Selection.Characters.Text = "Stop" Then
    Range("B" & i).Value = Now()
    Range("C" & i).Value = DateDiff("s", Range("A" & i).Value, Range("B" & i).Value)
    ActiveSheet.Shapes(1).Select
    Selection.Characters.Text = "Start"
    Range("C" & i).Select
End If

End Sub

My excel programming skills might not be perfect or effective, but for this purpose it should be sufficient. Make sure you don't forget the global variable. This also assumes your first button is the one holding this code (and it has text "Start" on it to begin with). Automatically calculates time used in seconds.

share|improve this answer
    
Tried this, but failed to compile. –  Avi Apr 16 '11 at 18:10
    
What version of Office/Excel are you using? This was made with the 2007 version, don't know if it works on earlier versions, but expected to work on the later one(s). –  Yohsoog Apr 19 '11 at 12:00
    
I'm using Excel 2010 –  Avi Jun 11 '11 at 10:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.