Inventory Barcode Scanner / Expiration Date help
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
-
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!
-
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!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 377 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!