• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 740
  • Last Modified:

To create a macro for MONTH column with corresponding date information

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:

1=Jan
2=Feb
3=March
....
12=Dec

I have data from Column A to J. so after inserting "Month" in column B, i will have columns from A to K.
0
ceneiqe
Asked:
ceneiqe
  • 3
2 Solutions
 
mark_harris231Commented:
Place this formula in B2 (assuming a header of "Month" or something similar in B1), and copy down:

=CHOOSE(LEFT(TEXT(A2,"mm/dd/yyyy"),FIND("/",TEXT(A2,"mm/dd/yyyy"))-1),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
0
 
mark_harris231Commented:
Actually, just use the formula:
=CHOOSE(MONTH(A2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
0
 
NorieCommented:
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

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

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now