How to create a date of manufacture and expiry date from a batch number

Hi all,

I have a query regarding the title of this post. The company I work for use the batch number format : 17030201: where 17 = year, 03 = month, 02 = day & 01 = manufacturing run of the day.

We then create batch certificates which require the date of manufacture (DOM) and an expiry date based on that DOM.

I'm having trouble setting a formula in a cell that will essentially take the first six digits of the batch number and convert it to a date & then another cell which will have an expiry date taken from the cell with the DOM - a pre-determined expiry date is given to each batch for example: 4 weeks, 6 weeks, 8 weeks, 3 months 6 months & 12 months.

Any help on this will be greatly appreciated.

Thanks,

Attach a sample workbook.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Date:

A1=Batch Number

=DATE(LEFT(A1,2)+2000,MID(A1,3,2),MID(A1,5,2))

ADD expiry period as number of days to get Expiry date:

28, 42, 56, 90 180, 365 (highlighted are "rounded" for 3 and 6 months)

Thanks for the formula, that's worked a treat! We don't work in weeks or days for 3 months, 6 months and 12 months so if there's a different formula for adding on complete months at the end that would be great!

Thanks again.

Originally Posted by misterzim

for 3 months,

Or try this ...

=EDATE(--TEXT(INT("20"&A1/100),"0000\/00\/00"),3)

@Tony,

Why ask for a sample file if you're not going to wait to see said file?

Imperfect that I am, I re-read the post and tried a solution:

And why did YOU think any manufacturing date would not be in the 2000s ?? (given an expiry date of max of 1 year!): hardly likely to going back 17 years.

See formula in post #8

=EDATE(--TEXT(INT(("20"&A1)/100),"0000\/00\/00"),4)

the result is 28/2/2019