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.

wrote:
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?

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

On 30 Oct 2006 16:01:32 -0800, wrote:

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.

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

Gord Dibben wrote:
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

On 30 Oct 2006 16:01:32 -0800, wrote:

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.

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 ToolsOptionsEdit"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


On 30 Oct 2006 20:44:27 -0800, wrote:

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

Gord Dibben wrote:
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

On 30 Oct 2006 16:01:32 -0800, wrote:

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.

Gord Dibben MS Excel MVP

Can this be done WITHOUT using a Macro?
I need this to be done on a shared drive, and cannot get it to save a Macro embeded file. =[ Please help

1. Select the cell where you want to insert the current date.
2. Press the "Ctrl" and ";" keys on your keyboard at the same time. This will insert the current date in the selected cell.
3. To insert the current time in a different column, select the cell where you want to insert the time.
4. Press the "Ctrl" and ":" keys on your keyboard at the same time. This will insert the current time in the selected cell.

Alternatively, you can use the following formulas to automatically fill in the current date and time:

1. For the current date, enter the formula in the cell where you want to insert the date. This will automatically update the date every time you open the spreadsheet.
2. For the current time, enter the formula in the cell where you want to insert the time. This will automatically update the time every time you open the spreadsheet.

You can also format the cells to display the date and time in a specific format. To do this, right-click on the cell and select "Format Cells". In the "Number" tab, select "Date" or "Time" and choose the desired format from the list.