Inventory Barcode Scanner / Expiration Date help

Paul Pasciuto

I made an inventory grid. One column has a barcode and one column has the expiration date. The expiration date reads the last 8 digits of the barcode to output a string of numbers that is the expiration date. Is it possible to make it formatted into a date like "dd/mm/yyyy". Right now it looks like this "ddmmyyyy"

Jeff Reisman

How is it reading the bar code? A scan gun acts just like a keyboard. If your Date column is set to only accept dates, a scan gun reading a bar code with 01011980 into a date-only column will input as 01/01/1980.

The only way I could really see to do this would be a series of reads off the bar code and insert slashes into a text approximation of a date, then maybe convert that text to a date?

Let me test something and get back to you.

Jeff Reisman

OK here you go:

I have two columns. "Text" is my bar code entry, Date is my date field.

In the Text column I have 1234503042020.

In the Date column I have: =MID(Text@row, 6, 2) + "/" + MID(Text@row, 8, 2) + "/" + MID(Text@row, 10, 4)

Which displays as 03/04/2020.

The MID function tells the system that in a given cell, start at X position and give me Y number of characters.

Jeff Reisman

One more thing - the above will display what looks like a date, however it is actually just text. Any date calculations won't consider this value as a date. The below formula, entered in a Date -formatted field, will render and actual date in MM/DD/YY format:

=DATE(VALUE(MID(Text@row, 12, 2)), VALUE(MID(Text@row, 6, 2)), VALUE(MID(Text@row, 8, 2)))

A COUNTIF statement against my date column will count that cell as a date correctly and return a 1:

=COUNTIF(Date:Date, <TODAY(0))