Date autoentry function

  • Thread starter Thread starter Pat
  • Start date Start date
P

Pat

I am trying to create a spreadsheet that will
automatically enter the date that data was manually
entered into the cell next to it.

I tried using the formula =IF(B5>0,TODAY()," ") where "B5"
is the cell the data is manually entered into, but the
date always changes to the current day whenever I open the
spreadsheet rather than remaining the date the data was
entered on. What am I doing wrong?

Thanks!!
 
Hi Pat!

You need a subroutine for this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If .Column = 1 Then ' Entry in column A
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
End With
End Sub



This goes in the code module for the sheet. You can go directly to
this by right clicking the sheet tab. This example gives date and time
but can be adapted to taste.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Thanks so much for the quick reply and the McGimpsey
website info. This will help a great deal now and (I'm
sure) in the future as well.

Thanks again!!
 
Hi Pat!

Always pleased to help especially if it saves your job.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top