Forum Home
Press F1
 
Thread ID: 104724 2009-11-06 06:55:00 Live times for excel? rob_on_guitar (4196) Press F1
Post ID Timestamp Content User
827669 2009-11-06 06:55:00 Just got a question to see if this can be done.
Am I able to set up an XL sheet so that if I clicked on a cell, it would automatically show the current time.
I want to see if this can be done for time keeping, so you click the cell and it records that time. Click the next cell and it records that time etc

TIA 		rob_on_guitar (4196)
827670 2009-11-06 08:41:00 Not sure about clicking but here are some kboard shortcuts.

Current date Select a cell and press CTRL+;

Current time Select a cell and press CTRL+SHIFT+;

Current date and time Select a cell and press CTRL+; then SPACE then CTRL+SHIFT+; 		ronyville (10611)
827671 2009-11-06 09:31:00 I recall John Walkenbach, an excel advanced expert, made live time clocks in excel (which I'm sure where on a PC World NZ magazine disc about 10 years ago). Maybe his tutorials are online somewhere. Or maybe install the Analysis Toolpak on the tools menu which may have live time display formats. kahawai chaser (3545)
827672 2009-11-06 10:05:00 Thanks guys thats very helpful. Those shortcuts are almost perfect, Ill try check out Walkenbach stuff too! rob_on_guitar (4196)
827673 2009-11-06 19:25:00 A possible solution is to use a little bit of VBA. If you open up the visual basic editor (Alt + F11) then select say sheet 1 and insert the following bit of code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Target = Now()
End If

End Sub

Now this is set up to insert the date and time if you click in any cell between A1 and A10 but obviously you can change this to whatever you range you want. If you choose not to include the 'if' statement then whenever you click any cell it will return the date & time which may be problematic if you have other workings on your sheet that you don't want to inadvertently overwrite.

HTH
Dave 		odyssey (4613)
827674 2009-11-07 06:45:00 A possible solution is to use a little bit of VBA. If you open up the visual basic editor (Alt + F11) then select say sheet 1 and insert the following bit of code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Target = Now()
End If

End Sub

Now this is set up to insert the date and time if you click in any cell between A1 and A10 but obviously you can change this to whatever you range you want. If you choose not to include the 'if' statement then whenever you click any cell it will return the date & time which may be problematic if you have other workings on your sheet that you don't want to inadvertently overwrite.

HTH
Dave

Spot on, excellent, thank you very much!:thumbs: 		rob_on_guitar (4196)
827675 2009-11-18 01:53:00 Sorry for my noobness, if I wanted to make it so there was extra columns used, say I wanted A01:A10 and also C10:C15 on the same worksheet, how I do I write the code to add extra columns?

Thanks 		rob_on_guitar (4196)
827676 2009-11-18 06:15:00 This should do it

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A10", "C10:C15")) Is Nothing Then
Target = Now()
End If

End Sub

Oops that's wrong - this would put the time in B1:B10 as well 		TeejayR (4271)
827677 2009-11-18 06:19:00 Yea I was trying that method too but always came back with an error rob_on_guitar (4196)
827678 2009-11-18 06:54:00 Try doubling up the code

i.e after the original, copy it, but replace the cell references. 		the_bogan (9949)
1 2