Inventory Barcode Scanner / Expiration Date help

Options

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"

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    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.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    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))

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!