The article will show you how to imply barcode scanner entry to excel database with timestamp. If you work in a shop, you need to keep a record of the time when a product is sold to a customer with its ID. Barcode provides us the ID of a product and also its property in some cases. To keep track of a product’s purchase and selling times, we need to enter the barcode scanner into Excel Database with a timestamp. So, it’s important to have the knowledge of how to put a barcode scanner with a timestamp.
Barcode Scanner Entry to Excel Database with Timestamp: 3 Basic Cases
In the dataset, we have some barcodes with corresponding timestamps.
1. Barcode Scanner Entry to Excel Database with Start and End Time
In this section, I will show you how to apply barcode scanner entries to Excel databases with a timestamp of both start and end times. Let’s go through the process below.
Steps:
- After that, the VBA editor window will appear. Select Insert >> Module.
- Thereafter, type the following code in the VBA
Sub BarcodeTimestamp()
Dim Bar_Code As String
Dim Barcode_Range As Range
Dim Row_Number As Long
Bar_Code = ActiveSheet.Cells(4, 3)
If Bar_Code <> "" Then
Set Barcode_Range = ActiveSheet.Range("B5:B100").Find(What:=Bar_Code, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Barcode_Range Is Nothing Then
ActiveSheet.Range("B5:B100").Find("").Select
ActiveCell.Value = Bar_Code
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Date & " " & Time
ActiveCell.NumberFormat = "dd/mm/yyyy h:mm:ss AM/PM"
ActiveSheet.Cells(4, 3) = ""
Else
Row_Number = Barcode_Range.Row
ActiveSheet.Range(Cells(Row_Number, 2), Cells(Row_Number, 10)).Find("").Select
ActiveCell.Value = Date & " " & Time
ActiveCell.NumberFormat = "dd/mm/yyyy h:mm:ss AM/PM"
ActiveSheet.Cells(4, 3) = ""
End If
End If
ActiveSheet.Cells(4, 3).Select
End Sub
We declared some necessary variables in the code, like Bar_Code As String, Barcode_Range As Range and Row_Number As Long. We also set the position of the Bar_code in C4 (ActiveSheet.Cells(4,3)) and used ActiveSheet.Range, ActiveCell.Value etc. properties of VBA and ran it. In addition, we set the Time Format to “dd/mm/yyyy h:mm:ss” so that we can properly differentiate the timestamps. You can use the pattern of my dataset because in that case, you don’t need to modify the code. Here is a preview of my dataset structure.
- Next, open the Worksheet on which you will enter the barcode scanner id from the VBA Project and type the following code in it.
Private Sub Worksheet_Change(ByVal target_value As Range)
If Not Intersect(target_value, Me.Range("C4")) Is Nothing Then
Call BarcodeTimestamp
Application.EnableEvents = True
End If
End Sub
We just called the BarcodeTimestamp Macro and enabled the Application Events by this Private Sub Procedure.
- After that, go back to your sheet and run the Macro.
- Next, type the barcode number in the corresponding cell.
- After that, press the ENTER button and you will see the Product ID with its timestamp of Start Time. This time refers to the time when your product is brought to the store.
- Suppose you sold this product. Just copy the Barcode number in the cell where you input the barcodes and you will see the End Time in the corresponding cell.
- If you want to update the timestamp, you can either delete the previous timestamp and apply for a new entry or you can simply copy the barcode I set some new cells to keep the updated timestamps.
- Later, in the following picture, I just filled the blank cells with random barcodes and their corresponding previous and updated timestamps.
Thus you can apply barcode scanner entries to excel databases with timestamps.
Read More: How to Use Barcode Scanner in Excel
2. Barcode Scanner Entry with Timestamp of Start Time Only
If you don’t require putting the end time as timestamps, you can use a much simpler procedure. Let’s go through it in the following description.
Steps:
- Follow the procedure of Section 1 to open the VBA Editor.
- Next, open the Worksheet on which you will enter the barcode scanner id from the VBA Project and type the following code in it.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As Integer
For x = 5 To 200
If Cells(x, 2).Value <> "" And Cells(x, 3).Value = "" Then
Cells(x, 3).Value = Date & " " & Time
Cells(x, 3).NumberFormat = "dd/mm/yyyy h:mm:ss AM/PM"
End If
Next
End Sub
Here, we declared x As Integer, used a VBA If Statement and some properties like Cells.Value and Cells.NumberFormat to create the timestamp.
- After that, go back to your sheet and type a barcode.
- Later, press the ENTER button and you will see the timestamp beside it.
- In the following picture, I just filled the blank cells with random barcodes and their corresponding timestamps.
Thus you can get barcode scanner entries to excel databases with timestamps.
Read More: How to Use Barcode Scanner for Inventory in Excel
3. Applying Excel Formula to Enter Barcode to Database with Timestamp
If you are not a VBA guy, you can apply IF and NOW functions to enter a barcode scanner to an Excel database with timestamp. It’s not going to be as effective as the previous methods though. Let’s go through the procedure below.
Steps:
- First, set your Calculation Option to Manual as the NOW function is a dynamic function which will always provide the current time after any command is executed.
- Thereafter, type any barcode in cell B5 and type the following formula in cell C5.
=IF(B5<>"",NOW(),"")
Here, the IF function operates a logical test of whether B5 is empty or not. If it’s not empty then the formula returns the current time with the help of the NOW function. Otherwise, it returns blank.
- Hit the ENTER button and you will see the corresponding timestamp.
- For the second timestamp and barcode, you need to type the barcode first, then type the following formula and press ENTER.
=IF(B6<>"",NOW(),"")
- After that, I filled the blank cells with random barcodes and timestamps in a similar manner.
Thus you can enter a barcode scanner to an Excel database with timestamps using Excel formula. The problem with this method is that you have to keep entering the formula manually.
Read More: How to Move Barcode Scanner to Next Row in Excel
Practice Section
Here, I’m giving you the dataset of this article so that you can practice these methods on your own.
Download Practice Workbook
Conclusion
In a nutshell, you will learn some easy and effective methods of how to imply barcode scanner entries to the Excel Database with timestamps. If you have any better methods or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.
