Custom Date format to comply with Canadian Food Regulations

[cdntester]

I hope that this is in the right forum.

I need to be able to convert dates from excel native format to the following (yy mm dd):

08 JA 30 is 2008 January 30th
15 AL 24 is 2015 April 24th


Where the month letters are as follows:
January: JA
February: FE
March: MR
April: AL
May: MA
June: JN
July: JL
August: AU
September: SE
October: OC
November: NO
December: DE

I have searched for an answer to this but have not been able to find an answer. The weird thing is that this format is the law in Canada for food labeling but no one seems to have ever enquired how to do it.

Can anyone help me?

[bebo021999]

With A1 is short date in text

=DATE(20&LEFT(A1,2),LOOKUP(MID(A1,4,2),{"AL","AU","DE","FE","JA","JN","JL","MA","MR","NO","OC","SE"},{4,8,12,2,1,6,7,5,3,11,10,9}),RIGHT(A1,2))

Note: This about converting 08 JA 30 to 30 JAN 2008 (if you are interresting in)

[p24leclerc]

How about this formula:

Please Login or Register  to view this content.

[cdntester]

IT WORKS!!!!!
=TEXT(A1,"yy")&" " &CHOOSE(MONTH(A1),"JA","FE","MR","AL","MA","JN","JL","AU","SE","OC","NO","DE")&" "&DAY(A1)

Thank You !!!!!
I think this will help many people in the future!!!!!

[MrShorty]

I don't know if it really applies to what you are doing, but one thing I would point out about Pierre's formula -- it converts a regular date serial number into a text string, which will not be easily used in other calculations. I would recommend that this sort of formula be used as a "dead end" type of thing. If you need this result to be used in other date/time calculations, keep a "copy" of the cell in regular Excel date/time format.

[cdntester]

Thanks for the follow up.

Indeed there will be no further calculations. An external program will take the text contents of the cell and use it to print the date.

Again, many
Thanks