Solved

# Recording the time in Excel

Posted on 2003-12-05
317 Views
Hi, everyone!

I would like to have a small timer in the background of my Excel application. Whenever the user changes a value in any cell in Sheet1, in the same cell in Sheet2 should be written how much seconds it took the user between two changes - that is, e.g., the user writes a value in B4 and then, 12 seconds later, another value in C15. Then, in Sheet2, in cell C15 would stand simply "12". It doesn't really matter, if the programme writes seconds or the standart time format.
I'm looking for a complete solution, that's why I am giving away the maximum points!
0
Question by:luchko
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 2
• 2

LVL 50

Expert Comment

ID: 9881443
hi luchko,

View Code
paste the sub below

Public startt

Private Sub Worksheet_Change(ByVal Target As Range)
startt = Timer
End Sub

Cheers

Dave
0

LVL 50

Accepted Solution

Dave Brett earned 450 total points
ID: 9881465
a bit tidier on the formatting

Public StartT As Double
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets(2).Range(Target.Address) = Format(Timer - StartT, "##0.0 /secs")
StartT = Timer
End Sub
0

LVL 10

Assisted Solution

ID: 9881517
Hi luchko,
Create a module in your workbook, and paste in the following:

Dim StartTime As Date
Sub ChangeTime()
StartTime = Now()
End Sub
Sub ChangeCell()
mc.Value = Now() - StartTime
ChangeTime
End Sub

In the Workbook open event (to initialise the value):

Private Sub Workbook_Open()
ChangeTime
End Sub

And in the Worksheet change event:

Private Sub Worksheet_Change(ByVal Target As Range)
ChangeCell
End Sub

Now, Sheet2 will contain times from opening the wokbook to entering the first cell, and the interval between cell changes.

Regards .. Alan
0

LVL 10

Expert Comment

ID: 9881520
luchko,
You don't really need a timer, the interval between one cell change and the next seems to be what you're looking for.
0

Author Comment

ID: 9881540
Thank you a great lot! Could you also please tell me if I could somehow use the usual format xx:xx:xx as well?
0

LVL 50

Expert Comment

ID: 9881564
sure, try

Public StartT As Double
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets(2).Range(Target.Address) = Format(Now() - StartT, "hh:mm:ss")
StartT = Now()
End Sub

Cheers

Dave
0

Author Comment

ID: 9881606
Excellent! I am very grateful to both of you!
0

LVL 50

Expert Comment

ID: 9881610
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applicâ€¦
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can launâ€¦
###### Suggested Courses
Course of the Month4 days, 22 hours left to enroll