Bin has attended:
Excel VBA Intro Intermediate course
Access Introduction course
Access Intermediate course
Access Advanced course
Excel VBA Advanced course
Excel VBA - separating month and year from date, inserting two c
Hi,
I want to insert two columns next to a date column in order to separate the month and year from the date in these two new columns.
Is there any macro i can use?
Thanks
Bin
RE: Excel VBA - separating month and year from date, inserting t
Hi Bin,
I suggest that you start the macro recorder and then record the macro.
You need the worksheet function =year(a1) if you have the first date in A1. This function will get the year from your date. To get the month you can use =month(a1). Then copy down the functions and stop the macro record.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Excel VBA - separating month and year from date, inserting t
Hi Jens,
I have created below code. is there anyway to extract only the month part as "mm" out of the date "dd/mm/yyyy" and then put in the new created column, in column f as per below.
Thanks
Bin
Dim NumRows As Integer
' Set numrows = number of rows of data.
NumRows = Range("e2", Range("e2").End(xlDown)).Rows.Count
' Select cell a1.
Range("f2").Select
' Establish "For" loop to loop "numrows" number of times.
For x = 1 To NumRows
' Insert your code here.
' Selects cell down 1 row from active cell.
ActiveCell = Cells(x + 1, 5)
ActiveCell.Offset(1, 0).Select
Next
Range("f2:f10000").Select
Selection.NumberFormat = "mm"
RE: Excel VBA - separating month and year from date, inserting t
Hi Bin,
If you have your dates in column E and want the months in column F and you know the range is starting from F2 then you do not need all the code you have in your example just use the macro below.
Sub ExtractMonth()
Dim lNumRows As Long
lNumRows = Range("F2").CurrentRegion.Rows.Count + 1
Range("F2").Select
Selection.Value = "=MONTH(RC[-1])"
Selection.AutoFill Destination:=Range("F2", "F" & lNumRows)
End Sub
This is a much faster macro than using a loop.
I hope this will help you.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Excel VBA - separating month and year from date, inserting t
Hi Jens,
Brilliant thanks Jens.
I used similar one but with Text(cell,"mm") instead for better using Pivot tables as per below.
Dim x As Integer
Dim NumRows As Integer
NumRows = Range("e2", Range("e2").End(xlDown)).Rows.Count
Range("f2").Select
For x = 1 To NumRows
ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""dd"")"
ActiveCell.Offset(1, 0).Select
Next
