Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Showing Month Abbreviation With Using 2 Characters

  1. 11-22-2013, 07:40 PM #1
    AppleofyourEye
    AppleofyourEye is offline
    Junior Member
    Join Date
    Jul 2013
    Posts
    3
    Rep Power
    0

    Showing Month Abbreviation With Using 2 Characters

    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)

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://eileenslounge.com/viewtopic.php?p=318868#p318868
    https://eileenslounge.com/viewtopic.php?p=318311#p318311
    https://eileenslounge.com/viewtopic.php?p=318302#p318302
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317857#p317857
    https://eileenslounge.com/viewtopic.php?p=317541#p317541
    https://eileenslounge.com/viewtopic.php?p=317520#p317520
    https://eileenslounge.com/viewtopic.php?p=317510#p317510
    https://eileenslounge.com/viewtopic.php?p=317547#p317547
    https://eileenslounge.com/viewtopic.php?p=317573#p317573
    https://eileenslounge.com/viewtopic.php?p=317574#p317574
    https://eileenslounge.com/viewtopic.php?p=317582#p317582
    https://eileenslounge.com/viewtopic.php?p=317583#p317583
    https://eileenslounge.com/viewtopic.php?p=317605#p317605
    https://eileenslounge.com/viewtopic.php?p=316935#p316935
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317014#p317014
    https://eileenslounge.com/viewtopic.php?p=316940#p316940
    https://eileenslounge.com/viewtopic.php?p=316927#p316927
    https://eileenslounge.com/viewtopic.php?p=316875#p316875
    https://eileenslounge.com/viewtopic.php?p=316704#p316704
    https://eileenslounge.com/viewtopic.php?p=316412#p316412
    https://eileenslounge.com/viewtopic.php?p=316412#p316412
    https://eileenslounge.com/viewtopic.php?p=316254#p316254
    https://eileenslounge.com/viewtopic.php?p=316046#p316046
    https://eileenslounge.com/viewtopic.php?p=317050&sid=d7e077e50e904a138c794e1 f2115da95#p317050
    https://www.youtube.com/@alanelston2330
    https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
    https://eileenslounge.com/viewtopic.php?p=316154#p316154
    https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
    https://teylyn.com/2017/03/21/dollarsigns/#comment-191
    https://eileenslounge.com/viewtopic.php?p=317050#p317050
    https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854
    https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Attached Files Attached Files
    Last edited by DocAElstein; 07-25-2024 at 01:48 PM.
    Reply With Quote Reply With Quote
  2. 11-22-2013, 08:56 PM #2
    Kenneth Hobson
    Kenneth Hobson is offline
    Member
    Join Date
    May 2013
    Posts
    31
    Rep Power
    0
    Welcome to the forum!

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

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://eileenslounge.com/viewtopic.php?p=318868#p318868
    https://eileenslounge.com/viewtopic.php?p=318311#p318311
    https://eileenslounge.com/viewtopic.php?p=318302#p318302
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317857#p317857
    https://eileenslounge.com/viewtopic.php?p=317541#p317541
    https://eileenslounge.com/viewtopic.php?p=317520#p317520
    https://eileenslounge.com/viewtopic.php?p=317510#p317510
    https://eileenslounge.com/viewtopic.php?p=317547#p317547
    https://eileenslounge.com/viewtopic.php?p=317573#p317573
    https://eileenslounge.com/viewtopic.php?p=317574#p317574
    https://eileenslounge.com/viewtopic.php?p=317582#p317582
    https://eileenslounge.com/viewtopic.php?p=317583#p317583
    https://eileenslounge.com/viewtopic.php?p=317605#p317605
    https://eileenslounge.com/viewtopic.php?p=316935#p316935
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317014#p317014
    https://eileenslounge.com/viewtopic.php?p=316940#p316940
    https://eileenslounge.com/viewtopic.php?p=316927#p316927
    https://eileenslounge.com/viewtopic.php?p=316875#p316875
    https://eileenslounge.com/viewtopic.php?p=316704#p316704
    https://eileenslounge.com/viewtopic.php?p=316412#p316412
    https://eileenslounge.com/viewtopic.php?p=316412#p316412
    https://eileenslounge.com/viewtopic.php?p=316254#p316254
    https://eileenslounge.com/viewtopic.php?p=316046#p316046
    https://eileenslounge.com/viewtopic.php?p=317050&sid=d7e077e50e904a138c794e1 f2115da95#p317050
    https://www.youtube.com/@alanelston2330
    https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-
    https://eileenslounge.com/viewtopic.php?p=316154#p316154
    https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg
    https://teylyn.com/2017/03/21/dollarsigns/#comment-191
    https://eileenslounge.com/viewtopic.php?p=317050#p317050
    https://eileenslounge.com/viewtopic.php?f=27&t=40953&p=316854#p316854
    https://www.eileenslounge.com/viewtopic.php?v=27&t=40953&p=316875#p316875
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 07-25-2024 at 01:49 PM.
    Reply With Quote Reply With Quote
  3. 11-22-2013, 09:26 PM #3
    AppleofyourEye
    AppleofyourEye is offline
    Junior Member
    Join Date
    Jul 2013
    Posts
    3
    Rep Power
    0
    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.
    Reply With Quote Reply With Quote
  4. 11-23-2013, 02:06 AM #4
    Kenneth Hobson
    Kenneth Hobson is offline
    Member
    Join Date
    May 2013
    Posts
    31
    Rep Power
    0
    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.
    Reply With Quote Reply With Quote
  5. 11-23-2013, 02:24 AM #5
    snb
    snb is offline
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    make your own customlist:

    Code:
    Sub M_snb()
  Application.AddCustomList Split("MA|FE|MR|AL|MA|JN|JL|AU|SE|OC|NO|DE","|")
End Sub
    Reply With Quote Reply With Quote
  6. 11-23-2013, 04:24 AM #6
    Kenneth Hobson
    Kenneth Hobson is offline
    Member
    Join Date
    May 2013
    Posts
    31
    Rep Power
    0
    For the UDF method, add this to a Module and use it as the comment demonstrates.
    Code:
    ' =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
    Reply With Quote Reply With Quote
  7. 11-23-2013, 11:23 AM #7
    Kevin@Radstock
    Kevin@Radstock is offline
    Junior Member
    Join Date
    Dec 2012
    Posts
    16
    Rep Power
    0
    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.
    Reply With Quote Reply With Quote
  8. 11-24-2013, 10:10 PM #8
    Rick Rothstein
    Rick Rothstein is offline
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    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.
    Reply With Quote Reply With Quote
  9. 11-25-2013, 03:15 PM #9
    snb
    snb is offline
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    or
    =TEXT(A1+180,"yyyy/") & MID("JAFEMRALMAJNJLAUSEOCNODE",2*MONTH(A1+180)-1,2) & "/" &TEXT(A1+180,"dd")
    Reply With Quote Reply With Quote
  10. 11-25-2013, 09:02 PM #10
    Rick Rothstein
    Rick Rothstein is offline
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by snb View Post
    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")
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Similar Threads

  1. Custom Charts in Excel :: Comparison RAG Chart Showing Tolerance Limits
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 06-13-2013, 09:40 PM
  2. Find Mismatch Characters:
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 0
    Last Post: 06-06-2013, 07:53 PM
  3. Extract Certain Characters From A Text String
    By bobkap in forum Excel Help
    Replies: 5
    Last Post: 05-24-2013, 06:25 AM
  4. Excel Chart Showing Values on Angle: Nightingale Chart
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 0
    Last Post: 10-17-2012, 06:39 PM
  5. Remove Special Characters :
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 3
    Last Post: 03-06-2012, 09:41 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules