1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved Format Excel cell to show month as text

Discussion in 'Business Applications' started by Ripperdan, Jul 10, 2019.

Thread Status:
Not open for further replies.
Advertisement
  1. Ripperdan

    Ripperdan Thread Starter

    Joined:
    Oct 28, 2009
    Messages:
    64
    First Name:
    Danny
    How do I format a cell so that when I enter a 1 it shows JAN, enter a 2 it shows FEB, enter a 3 it shows MAR and so forth. I can get it to work if I type a complete date but I only want to type the 1 or 2 digit month number.
     
  2. queen.jessica

    queen.jessica

    Joined:
    Jul 6, 2019
    Messages:
    5
    Formula: =TEXT(DATE(2011,A1,1),"MMM")
    where A1 is cell address

    Hope it helps:)
     
  3. Ripperdan

    Ripperdan Thread Starter

    Joined:
    Oct 28, 2009
    Messages:
    64
    First Name:
    Danny
    I appreciate your reply but unless I am missing something I think you misunderstand what I am after. Currently I have cell D14 Custom Formatted as "mmm". If I type a whole date such as 8-1-19, it puts "Aug" in cell D14. I don't want to have to put in an entire date, I just want to type in an 8 or 08 and have it put "Aug" in cell D14. Currently if I type just an 8, Excel puts "1/8/1900" in the formula window just under the ribbon puts "Jan" in cell D14.

    Secondly, is there a way to make the cell all caps so the months come out "AUG" instead of "Aug"?
     
  4. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    462
    First Name:
    Jack
    Hello Ripperdan,

    I believe that queen.jessica was suggesting the creation of a New Cell that can interpret your data entry of 1-12 and display the desired month. This could be accomplished through her formula (below) which I have adjusted a bit to avoid a potentially unwanted month placeholder and to force Upper Case text for the month returned. Note this also applies the current year to the new cell, based upon the cell being interpreted. This formula would be placed in the formula bar for your New Cell.

    EX: =IF(A2<13,IF(A2>0,UPPER(TEXT(DATE(YEAR(TODAY()),A2,),"MMM")),""))

    For Instance: Cell A1 - Enter a "1" then....... Cell (pick one) = JAN (formula result with the year appended but not displayed).​

    However, I have to ask if you are using the cell for additional computations? IF yes, I am not aware of a Number/Date Format that would allow you to enter only one digit and return a three month date (capitalized or not capitalized), that would also reflect the current year. Not to say that a formula does not exist, just that I have not seen that functionality in MS Excel (possibly through some VB code though - not my forte). Typically, MS requires a full date to properly evaluate date based computations and has strict interpretation rules to make that happen.

    Good Luck
     
  5. Ripperdan

    Ripperdan Thread Starter

    Joined:
    Oct 28, 2009
    Messages:
    64
    First Name:
    Danny
    I appreciate all the time you both took to address my problem. I guess I don't know enough about Excel to make it work.

    And yes, I am using the date in the cell for other computations. I'll just type the whole date in the cell as I have been doing.

    Thanks again for your help.
     
  6. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    462
    First Name:
    Jack
    Hello Again,

    Here is some VB code that should accomplish what you are trying to do. Please keep in mind that you will need to define the target range (Currently - Column D) to meet your specific needs.

    Good Luck!

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim setdate As Date, textmonth As String
    setdate = "01/" & Target(1, 1).Value & "/2000"
    textmonth = UCase(Format(setdate, "MMM"))
    Application.EnableEvents = False

    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Cells.Value < 1 Or Target.Cells.Value > 12 Then
    MsgBox "this entry is not a valid month"
    Target(1, 1).Value = ""
    Exit Sub
    End If
    If Not Intersect(Target, Range("
    D2: D100")) Is Nothing Then
    With Target(1, 1)
    .Value = textmonth
    End With
    End If
    Application.EnableEvents = True

    End Sub
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1229796

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice