×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

stop watch in excel

stop watch in excel

stop watch in excel

(OP)
I am tracking times for projecets in Excel. I would like to setup up something so I can click on a cell and have it record the curent time and date. I found the crtl-; and crtl-: shortcut keys. This gives me the curent date then the curent time. However I was looking for a one click solution.

thanks,

RE: stop watch in excel

Hello,

The following code will add the date and time if you click anywhere in Column G. Amend as necessary, or let me know which cells you are looking to add the date to.


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Column = 7 Then
    ActiveCell.Formula = Now()
End If
End Sub

THis code needs to go into the specific Sheet Code WIndow.

----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

RE: stop watch in excel

HDS:

The only way you're going to get the 1 keystroke approach to work is to write a macro.  I just used the macro recorder to setup the macro and assign the keystroke (in mine, I used ctrl+t).... after simplifying the recorded macro, this is what I came up with:

Sub Time_Stamp()
'
' Time_Stamp Macro
' Enters the date and time at which the macro is run
' in cells A1 and A2 respectively.
'
' Keyboard Shortcut: Ctrl+t
'
    Range("A1").Value = Date
    Range("A2").Value = Time
End Sub

Note: if you want to just copy the macro above into a module in VBA, you can set the shortcut key by going to Tools -> Macro -> Macros...  Then click on the macro you want to setup a shortcut for and click the Options... button in the lower right side.

You could also setup a command button and assign the macro to it (the macro would run when the button was clicked).  Let me know if you need any more help!

Good luck!

jproj

RE: stop watch in excel

Great Code!  I created a command button with the code listed above.  I was also able to basically create a stopwatch with the button.  I now want to be able to paste the times collected into an array.  I know how to copy and paste the value, but I can only seem to paste the value into the same cell.  Is there a way to index an array using VBA?  In other words, right now I have a time elapsed in cell K10.  I wrote the code that pastes the value of K10 into L10, but the next time I hit the button, I want to paste into L11, and then L12, etc.

Any help would be appreciated!

Thanks.

RE: stop watch in excel

Hello Frank Harper

use the line

range("L65536").end(xlup).offset(1,0).select

to paste to

----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

RE: stop watch in excel

Frank,
I use the following methods on my timesheet workbook.  To fill in the next empty cell with the timestep you need to determine the "active" cell.  I do this in two steps using excel formulas because I don't know enough VB to accomplish the equivalent in code.  Then I use a vb macro to stamp the value.

1.  Find the active cell.
Say that the list of elapsed times is in column a rows 2 to 20.

In an unused column, say column D,
let D1 = the number of values in the list = count(a2:a20)

The address of the first open cell in the list is:
=CELL("address",OFFSET(a2,D1,0))

2.  Stamp the time (or any other value you want) into the active cell using the following macro, which you may want to assign to a button on the worksheet (I like to use textboxes so they are easy to label, then right click>assign macro):

Sub stamp_active()
'
' Stamps time into active "end time" cell
' Macro 5/11/2004 by Brian Taylor
'

'  
    mystamp = Time
'  Note - "d2" below matches the example.  This is the cell that has the address of the active cell.
    Range("d2").Select
    activecelladdress = ActiveCell.Value
    Range(activecelladdress).Select
    Selection.Formula = mystamp
End Sub

RE: stop watch in excel

Another solution might be to go to www.xnotestopwatch.com.
This great little utility runs outside of Excel and allows you to post increments to a spreadsheet via a "stamp"

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close