Automate date & time entry

[Cheeris]

Hi, first question so be gentle if this has already asked. I've tried using
the search function on Office 2003and on this website (and also Google) but
can't find what I am after.

I have an Excel 2003 spreadsheet which is currently used as a simple
line-by-line text based log of company deliveries and is added to several
times each day. All the data is manually entered cell-by-cell.

The column A is date formatted and logs the date the delivery arrives.

Columns B and C record the date and time (respectively) of when the delivery
paperwork is passed to my dept. Currently, this is entered either manually or
using 'Ctrl-;' and 'Ctrl-Shift-;'.

Is there any way of automatically completing the cells in column B and C
when a date is entered in the same row in column A? e.g. if the cell in A1 is
filled with a date of dd/mm/yy (whatever that might be) is there a formula to
automatically complete B1 and C1 with the current (i.e. not identical) date
and time?

 

[Don Guillett]

Right click sheet tab>view code>insert this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count <> 1 Or _
IsDate(Target) = False Or _
Target.Column <> 1 Then Exit Sub

Target.Offset(, 1) = Date
Target.Offset(, 2) = Time
End Sub

or for 8/18/2009 16:46

change the 2 lines to
target.offset(,1)=now