Showing Month Abbreviation With Using 2 Characters

[AppleofyourEye]

I work in a factory where we use 2 letter month expiry abbreviations. I'm trying to get Excel to show it the same way, but it's limited to Jan, 01, January, etc.
Is there a way I can get it to show JA instead?
We have a spreadsheet that autopopulates a 6 month and 9 month expiration from the current date.
IE 01/12/2013 turns into =M1800+180 (2014/May/30) and =M1800+270 (2014/Aug/28)
So I can't just write a choose/if formula. I'm not sure there's any way of doing this, any help would be appreciated.
(I've attached a copy of an example as well)

[Kenneth Hobson]

Welcome to the forum!

You can use strings. In B1:
=YEAR(A1+180)&"/"&UPPER(LEFT(TEXT(A1+180,"mmm"),2))&"/"&DAY(A1+180)

[AppleofyourEye]

Thanks! This is great, other than it's showing MA for both March and May, JU for June and July etc. I guess that's an issue since we're just cutting off anything past the 2nd letter. These are the abbreviations we use:
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'm sure this makes everything way more complicated.

[Kenneth Hobson]

Right, which is why 3 characters are used for abbreviations for month names. If you don't mind using a UDF, what you want can be done and simplified.

[snb]

make your own customlist:

Sub M_snb()
  Application.AddCustomList Split("MA|FE|MR|AL|MA|JN|JL|AU|SE|OC|NO|DE","|")
End Sub

[Kenneth Hobson]

For the UDF method, add this to a Module and use it as the comment demonstrates.

' =Month2(A1+180)
Function Month2(aDate As Date) As String
  Dim d() As String
  Application.Volatile False
  d() = Split("JA FE MR AL MA JN JL AU SE OC NO DE")
  Month2 = Year(aDate) & "/" & d(Month(aDate) - 1) & "/" & Day(aDate)
End Function

[Kevin@Radstock]

HI
Maybe try incorporating the LOOKUP function.

=LOOKUP(MONTH(A1+180),{1,2,3,4,5,6,7,8,9,10,11,12} ,{"JA","FE","MR","AL","MA","JN","JL","AU","SE","OC ","NO","DE"})

Alternatively the VLOOKUP can be used as well.

[Rick Rothstein]

You can format your cells as General and then put this formula in B1 and copy it down...

=REPLACE(UPPER(TEXT(A1+180,"yyyy/mmm/d")),6,3,CHOOSE(MONTH(A1+180),"JA","FE","MR","AL","MA","JN","JL","AU","SE","OC ","NO","DE"))

Change the two 180's to 270's in the above formula and use it in cell C1.

[snb]

or
=TEXT(A1+180,"yyyy/") & MID("JAFEMRALMAJNJLAUSEOCNODE",2*MONTH(A1+180)-1,2) & "/" &TEXT(A1+180,"dd")

[Rick Rothstein]

or
=TEXT(A1+180,"yyyy/") & MID("JAFEMRALMAJNJLAUSEOCNODE",2*MONTH(A1+180)-1,2) & "/" &TEXT(A1+180,"dd")

Yes, using MID that way is a good idea. You can save a coupe of characters (and a concatenation) by moving the last slash into the last TEXT function call like this...

=TEXT(A1+180,"yyyy/") & MID("JAFEMRALMAJNJLAUSEOCNODE",2*MONTH(A1+180)-1,2) & TEXT(A1+180,"\/dd")