Automatically enter date and time but only update once.

[Guest]

I have a workbook that contains 14 sheets. I have a sheet for each month
followed by 2 sheets for information.

Each Month sheet has the following column headings associated from columns A
through J:-

Owner; from date; number of days; to date, address, ID, month, input by;
date; time.

I have to input data in columns A, B, C E, H, I and J.

Columns A and H are pick lists.

I have formulas in the following columns:-

Column C: =IF(ISBLANK(Cnn),"",+Bnn+Cnn)

Column F: =IF(ISBLANK(Enn),"",+Fnn+1)

I want column I to be populated AUTOMATICALLY (do not want to use the
Control and semi-colon etc ) with the current date (dd mmm yy format) and
column J to be populated with the current time (format hh:mm am/pm) only when
column H is not blank.

Once the date and time have been entered in columns I and J, I do not want
it to be updated with a new time the next time someone goes into the work
book or when the date changes the following day. It should only be populated
to ‘blanks’ is there is no data in column H

Additionally, I do not have any experience of creating macro’s or VBA and
therefore the information given needs to be plain!!

Any help greatly appreciated.

 

[Bob Phillips]

Here is some VBA to do it

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
With Target
If .Column = 8 Then
With .Offset(0, 1)
.Value = Date
.NumberFormat = "dd mmm yy"
End With
With .Offset(0, 2)
.Value = Now
.NumberFormat = "hh:mm AM/PM"
End With
End If
End With

ws_exit:
Application.EnableEvents = True

End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

 

[JE McGimpsey]

Take a look here:

http://www.mcgimpsey.com/excel/timestamp.html

 

[Guest]

Many thanks Bob, it works a treat.