EDITED: Convert 365 day production date code to expiration date value (x+91/x+180) to more readable format – Artificial Intelligence

Have chased my tail over this and even managed to lock up OpenAI’s new ChatGPT interface into a repeated cycle of errors or incorrect values. Trying to enter the entire code into one cell does not seem to have an easy way to parse out the actual value needed although the formulas given by could return actual dates they were always off by months or years.
Date Code Format MDDDY M is a letter A thru M (no I) indicating month A=1…M=12 skipping I
DDD is date of year on 365 day calendar
Y is last digit of year (nominal value of 2 or 3 until next year)
Original formula created by ChatGBT

"=DATE(RIGHT(A1),MATCH(LEFT(A1,1),{"A","B","C","D","E","F","G","H","I","J","K","L"},0),3+MID(A1,2,3))-1">

CODE A0103 returns 1/12/1903 but will return numerical value for letter that is correct except for inclusion of I (editing to remove and add "M" to sequence returns parse error)

=DATE(/2000+RIGHT(A1,1)+IF(RIGHT(A1,1)="0",10,0),MATCH(LEFT(A1,1),"ABCDEFGHIJKLM",0),MID(A1,2,3))

#N/A for A0103 (Jan 10,2023) not sure why ChatGPT insisted on reverting to 2000 and Again I is not an expected value since it resembles L it is skipped and sequence goes to M

=IFERROR(IF(MONTH(DATE(RIGHT(A1),1,MID(A1,2,3)))=MATCH(LEFT(A1,1),"ABCDEFGHIJKLNM",0),DATE(RIGHT(A1),1,MID(A1,2,3)),"Invalid date code"),"Invalid date code")

A0103 returns Invalid Date Code and again Julian sequence has "I" included
This was an attempt to verify correctly entered date code by judging that DDD corresponded to a day in the month of the month indicated by the Letter code in first digit.

=DATE(2020+RIGHT(A1),MATCH(LEFT(A1,1),{"","A","B","C","D","E","F","G","H","J","K","L","M"},0),MID(A1,2,2))

Truly the closest to the actual value but letter codes are one month off and the days are just a tiny bit off or completely wrong depending on whether correctly formatted code is entered. But successfully eliminated the ‘I" and stayed within this decade.

There are other responses but basically variations on the theme and I cant quite pinpoint where to adjust values without breaking formula in some way and even the most detailed recitation to ChatGPT to identify the issue was pushing it farther away till finally locked up in an error so cooling down and might try again after my headache goes away.

Was hoping someone here might see something simple to tweak and bring it all together.

Starting to think only way is to parse out the date code manually/formula and then work with the individual parts similar to my current mental/manual math.
Column A | Column B |Column C
| ——– | ——– |——–
| 1st digit| 2nd-4th | 5th
| A | 010 | 3
| =B2 + 91 | | = B1+180
| Hey google what’s the 101st day of 2023| 04/11/2023
| or 271st day of 2023| 09/28/2023

It works but it’s hard to explain to other associates and math varies if date is 2022 (3 digit code +91-365= x day of 2023) after the fourth of July +180 days is 2024 AND wording has to pretty much exactly as in table to get anything precise vs links to various tables/calendars. /

Can find formulas to convert mm/dd/yyyy into a 3 digit code but nothing seems to reverse it as far as I can tell so have to resort to running the gambit of calculating switching to browser keying up voice clearing out last search and speaking new day then opening inventory app to put in the newly deciphered date in for that particular sku lest I get pulled away and have to redo it for one or more items. Once logged others have access and entire crew alerted just wanted a simpler way to get over the initial pass of decoding.

Since Google can ”with the right sentence” spit out a valid date in user friendly format had hoped to either use google lens to lift text and possibly get values in a quicker fashion but haven’t found a good way to get a clear picture for starters and lens just pulls up all sorts of stuff.-

Sometimes I cheat and use an app called Day of the Year to pull up 3 digit value for today subtract 91 and go looking for codes before that date. Again less than ideal and hard to communicate to fellow associates.

First try at setting up a spreadsheet used a similar function to fetch current date in 3 digit format and post 2 numbers so less math more hunting but still wasted effort with no valid date to enter in system unless it’s incredibly close say within a week of best buy date I calculated / pulled from spreadsheet.

As this is not the only version of decoding needed I was hoping utilizing the OpenAI chat interface would give me a good outline that could convert other coding schemes in a more streamlined fashion but starting to think it’ll be easier to learn Kotlin and build an app that pushes the camera’s limits in picking up date code or allows manual entry to then convert in similar fashion to google voice search. Preferably logging it for future reference since doubtful of successfully sending result to "Beep" app automatically. Just having a simple way to key in code and get a quickly generated date would be enough to fairly easily manually post it to BEEP to maintain a record for future uses.

Other examples of codes I regularly deal with :: 8 digit code — last 2 digits — Letter Month/Last digit Year

• 12 / 18 /24 months depending on type of product
  Another vendor 8 digit code takes first 2 letters in a similar fashion
  So you can see where your average associate has little hope of properly processing the codes and even seasoned managers usually have to at least glance at a spreadsheet to verify how many months to add to a particular item.

Those items obviously last much longer than the 91/180 day products and to some extent can be eyeballed good enough for rotation purposes. So my initial focus was to find an easy way to calculate a properly formatted date for entering in to BEEP and that even the freshest associate could understand without needing a printed out table to cross reference for each individual item/vendor.

I did locate this snippet about EXCEL involving Julian and giving some indication of OpenAI ChatGBT’s fixation on using 2000 as a basic part of equation.
text

Which might make the spreadsheet formula or a prompt to enter 3 digit date plus last digit year a 2iable option for my priority items. Only other thought is some form of embedded table with

| Column A | Column B | Column C|Column D|
| ——– | ——– |———|———|
| 1/1/2023 | 0003 | 2742 | 1852 |
| | | 3652 | 3652 |
| | 3653 | 0013 | 0013|
|01/0/2024 | 0014 |2743 | 1852 |
and some form of search function allowing you to enter 4 digit code that will give 2 values from COLUMN B based of intersection with code and COLUMN C AND D

Perhaps the right phrasing or task setup up for google assistant but don’t know enough about it’s quirks to know if it’s even possible or easier than a dedicated app/spreadsheet with code entry field ready for input.

https://stackoverflow.com/questions/11455830/date-based-on-day-of-365-day-year

Some of the excel equations come close particularly if I use a ‘ preceding code and DO NOT include the Letter code which forces an error in sheets instantly. My modifications to the formula could get the right year value by chance for a particular value but it was not determined by the last digit and don’t know enough about the variables to even change the date outputted for a given date code.

EDIT/UPDATE*

1. Date code has changed

Discovered that as of last month the manufacturer has changed their date code to MYDDD when I received this weeks order.
So 1st digit Month / 2nd digit (Last number of year) / 3-5th 3 digit year.

2. Found some tips on this site ExcelTrick.com for handling some of the math issues.

3. Still haven’t quite understood how Excel/Sheets handles 3 digit dates or dates in general but temporarily have found a workaround of sorts.

a. Splitting the date code into 3 cells
This greatly simplifies some of the formulas from the start.

b. Rely heavily on AUTOMATIC functions by formatting cells/columns prior to data/formula entry (tip from ExcelTricks)

c. Using =Edate(start_date,months) to rectfy automatically generated serial number to current year and calculate 3/6 month best buy date that I was after.