Batch Number Generator

[jacob@thepenpoint]

Greetings All,

I am trying to create a worksheet to generate batch codes in the form: YMM999, using a single digit for the year, a 2 digit month, and a serial number for the actual batch number. What I would like is to be able to enter the current date, and have the year and month extracted, comparing the month to the month from the previous entry and either increasing the serial counter by one, or resetting it to 1 if the month has changed.

I don't have a problem doing the comparison, padding the serial number portion of the batch number with zeros, or getting it to sequence. I just keep running into a mental block trying to get the year into a form to be compared. I can use =MONTH() to get the 1-12 month value from a date, but I am not getting good years using the =YEAR() function.

I probably have blinders on, but I just don't see a way to get the year out of the date.

I will appreciate any input. Thanks.

[Andrew-R]

Are you trying to do this using a macro or formula? If formula then where are you getting the current date from?

[jacob@thepenpoint]

I am trying to do it using a formula, the user would input the date, and the formula would calculate off of that. It would be creating a table with searchable data as I would add the product and size being made under that particular batch number.

[Andrew-R]

OK, assuming your date is going to be in column A and the batch number is in column B then I'd go with something like this, starting at row 2

=IF(OR(A2="",ISERROR(MONTH(A2))),"",RIGHT(YEAR(A2),1) & TEXT(MONTH(A2),"00") & TEXT(IF(OR(LEN(B1)<3,ISERROR(VALUE(B1))),1,IF(MONTH(A2)=VALUE(MID(B1,2,2)),VALUE(RIGHT(B1,3))+1,1)),"000"))

That can then be copied down for as many rows as you want, but will remain blank until a valid date is entered in column A for each row.

[jacob@thepenpoint]

Brilliant! Thanks! I had everything working except getting the year portion, but it was in a very convoluted formula with helper columns and all kinds of fun stuff. Your solution works perfectly and keeps it all in a nice, neat cell without the need for helper columns.

Thanks

[Haseeb Avarakkan]

Also: B2, copy down.

=IF(A2="","",RIGHT(TEXT(A2,"yymm"),3)&TEXT(COUNTIF(B$1:B1,RIGHT(TEXT(A2,"yymm"),3)&"*")+1,"000"))

[jacob@thepenpoint]

Also: B2, copy down.

=IF(A2="","",RIGHT(TEXT(A2,"yymm"),3)&TEXT(COUNTIF(B$1:B1,RIGHT(TEXT(A2,"yymm"),3)&"*")+1,"000"))

This works great too. I don't quite understand the logic yet, but I'm working through it.

Thanks!

[T1ger]

Also: B2, copy down.

=IF(A2="","",RIGHT(TEXT(A2,"yymm"),3)&TEXT(COUNTIF(B$1:B1,RIGHT(TEXT(A2,"yymm"),3)&"*")+1,"000"))

How can I use that existing formula but add other text before the number, for example I would like the batch number to have the year as a single digit, followed by a 2 digit month then to say mea001 or veg001

[Haseeb Avarakkan]

Hello T1ger, welcome to the forum!

As per forum rules, you can't post a question in the thread of another member, start your own thread. If you feel it's particularly relevant, provide a link to your new thread.