How to make excel auto fill in date and current time

[alice]

Is there a way to make an excel spread sheet with columns that, when
clicking in a cell, will automatically fill in the current date, and in
a different column do the same with the current time? I've looked under
Format Cells, Auto Format, and Conditional Format, and can't find
anything of the sort, but I thought I heard somewhere that this is
possible.

 

[alice]

Is there a way to make an excel spread sheet with columns that, when
clicking in a cell, will automatically fill in the current date, and in
a different column do the same with the current time? I've looked under
Format Cells, Auto Format, and Conditional Format, and can't find
anything of the sort, but I thought I heard somewhere that this is
possible.

Oh, and to make matter more confusing, at least for me, I still want
the first cells in the columns to have the headers of DATE and TIME.
Is it some kind of formula I need to insert?

 

[Gord Dibben]

Alice

You can enter the date in a cell by hitting CTRL + ;

Time by hitting CTRL + SHIFT + ;

To have it automatically entered would require event code that would enter the
date in one cell and the time in another.


Gord Dibben MS Excel MVP

 

[alice]

Is it difficult to deal with using "event code"?

Alice

You can enter the date in a cell by hitting CTRL + ;

Time by hitting CTRL + SHIFT + ;

To have it automatically entered would require event code that would enter the
date in one cell and the time in another.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Not so difficult it can't be done..

Right-click on the sheet tab and "View Code"

Copy/paste the code into that sheet module. When you double-click on any cell
in the range A1:A100 the date will be entered in that cell and the time will be
entered in the adjacent Column B cell.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target _
As Range, Cancel As Boolean)
Const myRange As String = "A1:A100"
On Error GoTo endit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(myRange)) Is Nothing Then
Target.Value = Format(Date, "dd mmm yyyy")
Target.Offset(0, 1).Value = _
Format(Now, "hh:mm:ss AM/PM")
End If
endit:
Application.EnableEvents = True
End Sub

Since most people have Tools>Options>Edit>"Edit directly in cell" checked, I
also provide code to disable that feature when you activate the sheet and
re-enable when you deactivate the sheet.

Paste to same sheet module if you think you will need it.

Private Sub Worksheet_Activate()
Application.EditDirectlyInCell = False
End Sub

Private Sub Worksheet_Deactivate()
Application.EditDirectlyInCell = True
End Sub


Gord

Is it difficult to deal with using "event code"?

Gord Dibben MS Excel MVP