Auto change date formats

**Bernerd Ern** · 02-02-2016, 05:49 AM

Hi all, I have a stock file that I want to change the format of the date based on a reference. I have a database on one tab with the stock codes and number of other related references and a second tab with a form.

The form is created by looking up the stock code and pulling through relevant information (mainly using INDEX, MATCH). It has a field to calculate the best before date by taking today's date (on the form) + the number of shelf life days (entered into a specific field) +90, +120 etc.

However, I want it to then format it based on a chosen format for that stock code.

Date code formats notes

1. DD/MM/YY
2. DD/MM/YYYY
3. DD.MMM
4. DD/MMM/YY
5. DD/MMM/YYYY
6. MM/DD/YY
7. MM/DD/YYYY
8. YY/MM/DD Month is: JA,FE, MR, AL, MA, JN, JL, AU, SE, OC, NO, DE
9. ddd Julian code (day of the year)

Is there away that I can give each stock code on the other tab a specific format? So that it calculates the best before date and changes the format to the specific format required?

Many thanks
Ben

**Tony Valko** · 02-02-2016, 07:02 AM

I don't have a solution , just a comment...

Why would you want to use all those different date formats? Seems like it would just add confusion.

1. DD/MM/YY
6. MM/DD/YY

If the entered date was 03/12/15 how would you know whether it was Mar 12 2015 or Dec 3 2015?

**tim201110** · 02-02-2016, 07:05 AM

yes, it is

**Bernerd Ern** · 02-02-2016, 07:42 AM

Hi, the date is printed on labels which are applied to products in the customer specific format. Having the form in the same format will allow it to be easily checked....hope that makes sense.

**Tony Valko** · 02-02-2016, 07:46 AM

OK, makes sense.

**Tony Valko** · 02-02-2016, 08:04 PM

If there is some kind of identification value that corresponds to a specific format then you could use conditional formatting to do this provided you're using Excel 2010 or later. For example...

Customer1 would use date format DD/MM/YY
Customer2 would use date format DD/MM/YYYY
Customer3 would use date format DD.MMM
etc
etc

We'd have to do something different for formats 8 and 9 but that should be doable.

**Bernerd Ern** · 02-03-2016, 06:21 AM

Hi, I did see somewhere that conditional formatting may do it, but struggled to get it to work.

I've attached an example file. If you think it's possible could you do it on such a file?

Also, to be extremely cheeky - I've now been asked to include images, which again seems to be beyond me although I know it is possible - I've also included in the attached example. I can post as a new thread if required.

Thank you guru's!