VBA worksheet_change - Date & Time in a cell

If anyone can answer this question .. YAY.
I have two columns within a spreadsheet. I want a date & time value in each column depending on when the field was entered.

My problem is using NOW() .. this updates to the current time and date for all fields within the column.

Is there anyway to add a date & time within a field and lock the values once they are entered?

Ultimately, I would like to calculate the exact time between the two fields.

HELP!

Re: Time & Date

I was unaware of those commands .. so it will help with my knowledge, but not with this situation.

The officers that will enter the data into the field (besides being old school idiots) can not be trusted to enter time (for they can alter it to better their statistics) .. so I was hoping for a way that they could enter the date, and a time appear without their knowledge or .. appear automatically into the same field or another which I can lock.

dd/mmm hh:ss is the format I am playing with .. but without the officer entering '14/12 9:56' into the field, I can not seem to get what I want.

Is there anyway to enter a date into one field and the time appear in either the same field by way of cell format or another field, automatically entering the time that the 1st field was entered?

Re: Time & Date

I can have it set so that once they enter a loan number into column A1, column B1 inserts a date and time .. via NOW() .. BUT .. if the user then goes to A2 to enter a second loan, B2 has the current time added, but A2 also updates to the current time.

Is there anyway to set it so that A2 stays static within a formular??
Or is the only way via VBA coding or the manual shift+; ??

and guys .. thanks heaps for your help on this

Re: Time & Date

I don't wan to use it .. as I am only just beginning to learn it .. so I am not yet up to speed with coding.

But .. if it will take coding to do what I want, then coding I will have to learn quicker.

Thanks heaps boys

VBA worksheet_change - Date & Time

I was given this example to add a date and time into a cell using worksheet_change.

As far as I have been told, once I put a value into A2, B2 should return a NOW() figure.

I have added this code into a module, but nothing happens within B2.
Am I doing something wrong? (well clearly if it's not working) Would be great if someone could help me out.

Basically, what I am trying to do, if an officer puts a loan number into A2, then I wish the current date and time to be put into B2. A2 is blank open opening the workbook.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
        With Target
            If .Count > 1 Then Exit Sub
            If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
                Application.EnableEvents = False
                If IsEmpty(.Value) Then
                    .Offset(0, 1).ClearContents
                Else
                    With .Offset(0, 1)
                        .NumberFormat = "dd mmm yyyy hh:mm:ss"
                        .Value = Now
                    End With
                End If
                Application.EnableEvents = True
            End If
        End With
    End Sub

Re: VBA worksheet_change - Date & Time

d0uga1 .. you are a legend.
Thanks heaps buddy