stop watch in excel

[HDS]

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,

 

[onlyadrafter]

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!

 

[jproj]

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

 

[FrankHarper]

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.

 

[onlyadrafter]

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!

 

[bltseattle]

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

 

[nide]

Another solution might be to go to

http://www.xnotestopwatch.com.

This great little utility runs outside of Excel and allows you to post increments to a spreadsheet via a "stamp"