Auto fill Date and Time

Looking for Current date and time.  What we have is an inspection sign in log.  Whenever someone from production needs an item inspected, they enter the info into this spreadsheet.  We track how long it takes inspection to return the time back to production.  I am trying to make this as easy and mistake free as possible for production by eliminating as much typing as I can.  The operator is to scan their employee badge when they go to sign in a part to be inspected. When this happens, I want the spreadsheet to auto fill the date and time.  This date and time needs to be static.  Then the operator can continue to scan the rest of the needed information.

Let's say you want the date to be entered in cell W1 and the time in cell W2.

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("W1").Value = Date
Range("W2").Value = Time
Application.EnableEvents = True
End Sub

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open it.

I got it. Thanks for all your help. I would have never gotten there without it. Solution shown below.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("D:D")) Is Nothing Then 

    Range("A" & Target.Row).Value = Date 

    Range("B" & Target.Row).Value = Time 

End If 

End Sub

Your first option is to use a few shortcut keys: Ctrl+; enters the current date, Ctrl+: enters the current time.  These entries are static, they will not change.

A formula won't work because it can't be both active and static, so another solution is a macro.  Let's suppose there are three columns A:C with the date and time in columns B and C.  You can create a macro that automatically enters the date and time in columns B:C when an entre is made in column A.  Here is a quick example:

In this case you would format column B to a date format and column C to a time format.  Anytime a change is made to an entry in column A the macro runs.  This is what's known as an event macro, in this case a Worksheet Change which has been add to the code sheet for the worksheet where the data is to be entered.  Their are some possible issues with this approach but I've tried to make it as simple as possible.

A fancier version might be something like this:

In this case columns B and C don't need to be manually formatted.

The first question we need to ask is are you auto filling the date or the time or both?  Regardless, what is your time increment, for example every 15 minutes, every hour, every 6 hours.  For that matter what is the date increment.  Please give us an example.