To create a macro for MONTH column with corresponding date information

Posted on 2012-09-16
Last Modified: 2012-09-16
Column A= Date
To insert a MONTH column in Column B that states "Jan" " Feb" etc base on the corresponding dates in Column A where dates are in MM/DD/YYYY format. Thus, if the date shows 01/01/2012, then is would be "Jan" - the first number dictates the month:


I have data from Column A to J. so after inserting "Month" in column B, i will have columns from A to K.
Question by:ceneiqe
    LVL 10

    Expert Comment

    Place this formula in B2 (assuming a header of "Month" or something similar in B1), and copy down:

    LVL 10

    Assisted Solution

    Actually, just use the formula:
    LVL 33

    Accepted Solution

    Put this formula in B2 and copy down.

    =TEXT(A2, "mmm")

    If you want code:
    Sub InsertMonth
    Dim LastRow As Long
          LastRow = Range("A" & Rows.Count).End(xlUp).Row
          Range("B1").EntireColumn.Insert Shift:=xlToRight
          Range("B1").Value = "Month"
          With Range("B2:B" & LastRow)
                .Formula = "=TEXT(A2, ""mmm"")"
                .Value = .Value       ' replace formulas with values - optional
          End With
    End Sub

    Open in new window

    LVL 10

    Expert Comment

    +1 to imnorie's TEXT() solution.  Significantly more elegant than the CHOOSE() method I presented.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now