Auto insertion of date and time

  • Thread starter Thread starter MC
  • Start date Start date
M

MC

I'm trying to build a spreadsheet to log support calls, all i need is a way
of inserting the time & date in a column as soon as data is entered in
another column. I have done this using the Now() function but the date &
time keep updating, I want them to remain the same as when the first
appeared.

I have a bit of VB knowledge so am happy to do it this way if required.

Any ideas?


TIA,

MC
 
You basically just need to create the date/time using the now() functio
and then do copy.. paste special.. values.

ActiveCell.Formula = "=NOW()"
ActiveCell.Copy
ActiveCell.PasteSpecial Paste:=xlPasteValue
 
At the moment there is a simple function saying if A1 is blank do nothing,
else insert the now() function
 
Hi
if you want an automatic solution then you need VBA :-)
so try the solutions in the link I provided to you :-)
 
From a post I sent to someone yesterday

right click sheet tab>view code>insert this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 4 And Target.Column = 3 Or Target.Column = 4 Then
Cells(Target.Row, "a") = Date
Cells(Target.Row, "b") = Time
End If
End Sub
 
Copy the code from John's page.

Right-click on your sheet tab and "View Code".

Paste John's code in there.

The code is worksheet event code and does not go into a general module.

You do not run it from Tools>Macro>Macros. It runs when the data is entered
in the Target cell.

Gord Dibben Excel MVP
 
Then you might want to take a look at the simpler one I posted a few days
ago.
right click sheet tab>view code>insert this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 4 And Target.Column = 3 Or Target.Column = 4 Then
Cells(Target.Row, "a") = Date
Cells(Target.Row, "b") = Time
End If
End Sub


--
Don Guillett
SalesAid Software
(e-mail address removed)
Illustris said:
Hi Frank,

I'm trying to create a Macro from http://www.mcgimpsey.com/excel/timestamp.html

However, whenever I try to run the Macro, the Macro won't appear in my
Click to expand...
Macro list. Can you help? The module is there, as is the code. I'm using
Excel 2003.
 
I used Don's code - worked a treat, thanks!


Don Guillett said:
Then you might want to take a look at the simpler one I posted a few days
ago.
right click sheet tab>view code>insert this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 4 And Target.Column = 3 Or Target.Column = 4 Then
Cells(Target.Row, "a") = Date
Cells(Target.Row, "b") = Time
End If
End Sub


--
Don Guillett
SalesAid Software
(e-mail address removed)

Macro list. Can you help? The module is there, as is the code. I'm using
Excel 2003.
Click to expand...
 
Back
Top