# Calculated Values from Formulas in Excel Cells

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?

-
@Lance - Thanks! – Avi Apr 14 '11 at 22:00

Ctrl + ; will insert the current date into a cell as a fixed value. Combine with Shift to get the current 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 ;.

-

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.

-
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). – Yoh Apr 19 '11 at 12:00
I'm using Excel 2010 – Avi Jun 11 '11 at 10:21