PDA

View Full Version : Live times for excel?



rob_on_guitar
06-11-2009, 06:55 PM
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

ronyville
06-11-2009, 08:41 PM
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+;

kahawai chaser
06-11-2009, 09:31 PM
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.

rob_on_guitar
06-11-2009, 10:05 PM
Thanks guys thats very helpful. Those shortcuts are almost perfect, Ill try check out Walkenbach stuff too!

odyssey
07-11-2009, 07:25 AM
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

rob_on_guitar
07-11-2009, 06:45 PM
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
18-11-2009, 01:53 PM
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

TeejayR
18-11-2009, 06:15 PM
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

rob_on_guitar
18-11-2009, 06:19 PM
Yea I was trying that method too but always came back with an error

the_bogan
18-11-2009, 06:54 PM
Try doubling up the code

i.e after the original, copy it, but replace the cell references.

TeejayR
18-11-2009, 06:59 PM
Try This

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

MyRange = ("A10:A15,C16:C20")

If Not Intersect(Target, Range(MyRange)) Is Nothing Then

Target = Now()
End If

End Sub

rob_on_guitar
19-11-2009, 07:30 AM
'O' for Awesome!
Choc fish all around!