Recording a Data Entry Time

You might be interested in the comments for this related Tip:
https://excelribbon.tips.net/T013125_Getting_Rid_of_All_Rows_Except_the_One_for_the_Latest_Date.html

@Tariq
You already know the answer. The macro in your question of 27 November 2018 in https://excelribbon.tips.net/T013233_Automatically_Entering_a_Data_Entry_Time.html does the job.

This was very helpful code. However, I would like to know how can we add date in Column A and time in Column B automatically with data entry in column C? I want time and date in two separate columns.

@Peter Machado

You can use my version of the macro I posted on 30 Nov 2013. It doesn't have this bug.

Hi

I noticed if I delete the record, then the subsequent record now becomes the new current record, and it's time stamp gets updated. Can you put in a check for delete so if the record already existed it doesn't get updated?

Thank you.
VERY Clear and Concise.

I want to make time sheet for full day meeting. There are several attendee who come and go frequently.
I want two button on top of two column for IN and OUT button named for each attendee and when pressed, it enters the current time. for next event when button is pressed, the data should be written to the next row for each attendee. this goes on for our weekly meeting for years to come.

@Peter Wessel

In which column do you want the date to appear?

My amateur radio log I set up in Excel, and it works fine. But as I am going to take part in a radio contest, I need to submit a log in another format - also quite simple to set up. A contest is fast, and seconds count, so I found your macro suggestion for the automatic time input - and it works beautifully - Thank you! However, due to the format of the log I need to auto-enter the time in a column BEFORE the one I enter data into. Would be super if I knew how to do that.....

Marissa: It happens because running a macro always clears the "undo stack." In other words, after running a macro you can't undo anything that occurred before the macro.

There is no way around this behavior that I've been able to find.

-Allen

Hi, I found this script and love it - except it has apparently removed the ability to Undo an action in any column prior to the one I set the macro to run. Instead of Column A:A, I used E:E, because that's where I want the timestamp to be based on. However, not being able to Undo actions in columns A-E is a dealbreaker. Any tips on why this is happening and how to fix it?

@Peter,

The macro to do what you want is:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B13")) Is Nothing Then Range("J10").Value = Now
End Sub

Please note that this needs to be entered on the codepage for the worksheet that the two cells reside upon.

Note: also the B13 is updated if J10 is edited and the value remains unchanged after the edit, or if the value is change to the same value. If this is a problem then then following slightly more complicated pair of macros will work:

Public TempVal as Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target,Range("J10") is Nothing Then TempVal = Target.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target,Range("J10") is Nothing Then Exit Sub
If Target.Value <> TempVal Then Range("B13") = Now
End Sub

Again these macros need to reside on the codepage for the worksheet concerned. NB TempVal is declared outside of any macro.

@Peter
Here it is:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$13" Then Exit Sub
If Target <> "" Then
Range("J10") = Now
Range("J10").NumberFormat = "hh:mm:ss"
Else
Range("J10").Clear
End If
End Sub

Hi I would like to use something similar. But i want for example Cell J10 to contain the date that will auto update and change when data in B13 is changed. Is it possible to do it for cell specific instead of whole row or column

@Dipak,

Just format the whole column using the time format that you want.

The value returned by the "Now" function includes the time as well as date.

hi,
i am in process engineering
i want to add my data in time formate (hh:mm) in developer code as a range

@Alex,

Sorry for the typo, the code should be:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("F4:O91")) Is Nothing Then Range("P" & Target.Range("A1").Row).Value = Now

End Sub

or

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("F4").CurrentRegion) Is Nothing Then Range("P" & Target.Range("A1").Row).Value = Now

End Sub

Note also if the change is made across multiple rows only the first row will have its timestamp updated.

@Alex

The following code will do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("F4:O91")) Is Nothing Then Range("P" & Targetrange("A1").Row).Value = Now

End Sub

If your range is changing as new records are added/deleted instead of using the fixed range "F4:O91" use "Range("F4").CurrentRegion"

This must be on the codepage of worksheet containing the data.

Hello,
I am wanting to have a date stamp anytime there is a cell changed/updated within each row. For example row 4 through 91 and columns f through o have data. I want the date at then end of each row, in column p. thanks

Hello again,

In the meantime I've found the following code that is perfect for me (I need the time of the scanning of some barcodes) as it does a "copy/paste value" of the Time so that my records are fixed once for all.
I've even added a second With in which I add the date on a third column.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
With Target(1, 2)
.Value = Time
.EntireColumn.AutoFit
End With
End If
End Sub

Thanks for all the tips on this website!!!

Hello,

I'm trying to use the code given by Willy on 30 nov 2013 (see here below) but I have an issue.

Starting from an empty cell, when I entered some data, nothing happens but if I go back in the cell where I've entered the data, then the time will appear - why is that? Could anyone help me?

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
   If .Column <> 1 Or .Columns.Count > 1 Then Exit Sub
   If .Cells(1) > "" Then
      .Offset(0, 1) = Now
      .Offset(0, 1).NumberFormat = "hh:mm:ss"
   Else
      .Offset(0, 1).Clear
   End If
End With
End Sub

Angela:

To do what you want, replace the For...Next loop with the following:

For Each rCell In rChange
If rCell > "" Then
If rCell.Offset(0, 1) <> "" Then GoTo ExitHandler
With rCell.Offset(0, 1)
.Value = Now
.NumberFormat = "hh:mm:ss"
End With

Else
rCell.Offset(0, 1).Clear
End If
Next

I am wanting to write customer details in column A, and last name in column B

When I do this I want an automated date in C, and time in D.

When I click in E I need an automated time (this will be the end of the job).

And then have F calculate the time taken between D and E

Could some please assist with this.

Thanks :)

I apologize in advance for commenting on an old tip.

I'm using something very similar to the above code, but I have a small problem.

I want the time entered into column A when text is entered into column B, but I don't want the time changed if you go back to a previous line to edit column A. Is this possible with Excel, and if so can you assist me with the proper code?

I've been searching for a week, and I'm at wit's end!

Hi,

I would like to record changes made to the data in columns 1,6,7,8,9 solely in column 10, could anyone help with this please?

@Willy: tbh I didn't think about what the first line meant, so I didn't bother with it. I didn't want to do ALL the work for Sky ;-)

@Sky: without any details there's not really any way to help.

@Bryan: your solution is the classic one and will of course work fine except when you delete an entire row. Then you will get an error: .Offset(0,1).Clear doesn't work with an entire row. Therefore you must disallow the macro to be executed when more than one column is selected. Hence "... Or .Columns.Count > 1 Then Exit Sub" in my macro.

Thanks both,exactly what I needed, I tried to protect the sheet except the columns in which the data should be entered manually and the date column but then a debug message appears and it doesn't work anymore.

@Sky: If you use my macro (see comment 30 Nov 2013) then replace

If .Column <> 1 Or .Columns.Count > 1 Then Exit Sub

With

If InStr("1,3,5", .Column) = 0 Or .Columns.Count > 1 Then Exit Sub

If you now enter data in column A, C or E, the date will appear in column B, D or F respectively.

Sky, I'm not really sure what you are asking. If you want the rule to apply to another column then use another column reference.

Or do you want to put the time in B when A is changed, D when C is changed, and F when E is changed? Use Willy's code with a slight modification:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
Select Case .Column
Case 1, 3, 5 ' Column A, C, or E
If .Value > "" Then
.Offset(0, 1) = Now
Else
.Offset(0, 1).Clear
End If
End Select
End With
End Sub

I mean how the formula changes if I want that rule applies to columns a,c,e at same time.

anyone?

what about if I want to do the same thing for columns C,E & F? I mean as soon as I put data in C then the time of data entry appears in D cell next to it and so on. But altogether at same time not just one column.

Nitin: Instead of going into the sheet module, you would instead go into the ThisWorkbook module and add the following code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Sh.Range("J1")) Is Nothing Then Exit Sub
Sh.Range("J1").Value2 = Now
End Sub

If you only wanted a few sheets to update (say, Sheet1 and Sheet2, but not Sheet3), you could do something like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Sh.Range("J1")) Is Nothing Then Exit Sub
If Not InStr(1, "Sheet1;Sheet2;", Sh.Name & ";") > 0 Then Exit Sub
Sh.Range("J1").Value2 = Now
End Sub

Note that the more hard-coding of data you do, the more likely you (or your user!) are are to break it without realizing it. For instance, in the second macro, if a user changes Sheet1 to IncomeSheet, then the last edit date won't update anymore.

There are certainly ways to get around this issue, but that's a separate problem. The examples I've posted should be good enough to get you started.

Hi Bryan,
Tried - It worked... But One question - If I have many sheets in the work book, & I want the same code to run for each sheet & the cell "J1" in each sheet to be updated with the 'now' - Do i need to copy the code on each sheet ? or is there a universal way to apply it to more than one or all worksheets at a time??

Thanks Bryan....
Shall try it right away!

@Willy: good job! Your solution is soooo much more elegant.

@Nitin: you would want something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("J1")) Is Nothing Then Exit Sub
Range("J1").Value2 = Now
End Sub

This macro will update cell J1 any time any cell on the worksheet is changed. If you only want to update J1 when a certain range is updated (e.g. C9:C22), then try this instead:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C9:C22")) Is Nothing Then Exit Sub
Range("J1").Value2 = Now
End Sub

Hi,
I tried this & it does work, But i would like only a particular cell in that sheet to display the last edited record time.
which means that if any cell in the sheet is entered / modified, say cell J1 will display the last edit / entry time in the relevant sheet.
Please advise.
Regards,
Nitin Sheth

For column A:
If .Column <> 1 Or .Columns.Count > 1 Then Exit Sub

For column B:
If .Column <> 2 Or .Columns.Count > 1 Then Exit Sub

etc...

Hi Willy,

Where do I put column name ? (A, B...)

Obviously this can't be solved by a formula. But the macro presented here… that's quite a macro :-)
There is no need at all for a For Each loop here. Target represents the selected range and you can use it accordingly. Neither must Application.EnableEvents be used here because the macro doesn't make any changes to the monitored column so that an endless loop can't occur.

The 27 lines macro above can be replaced by my rather simple 11 lines one that does its job more efficiently of course:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column <> 1 Or .Columns.Count > 1 Then Exit Sub
If .Cells(1) > "" Then
.Offset(0, 1) = Now
.Offset(0, 1).NumberFormat = "hh:mm:ss"
Else
.Offset(0, 1).Clear
End If
End With
End Sub