Excel VBA - separating month and year from date, inserting two c

Resolved · High Priority · Version 2010

Tue 19 Aug 2014, 17:31 Delegate Bin said...

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

Wed 20 Aug 2014, 13:53 Trainer Jens said...

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

Wed 20 Aug 2014, 13:58 Delegate Bin said...

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"

Wed 20 Aug 2014, 20:28 Trainer Jens said...

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

Wed 20 Aug 2014, 20:37 Delegate Bin said...

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

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Return to the active cell after scrolling

When I scroll a long way down the screen from a selected cell, I can return to that cell with the Ctrl+Back Space shortcut. The active cell now appears in roughly the middle of the screen.

Shift+Back Space does something similar. Scroll down from the active cell and Shift+Back Space returns me to it and puts the active cell at the top of the screen; scroll up from the active cell and Shift+Back Space returns me to it and puts the active cell at the bottom of the screen.

Note also, that while Ctrl+Back Space will return me back to a selected range, Shift+Back Space only ever returns me to the active cell, which is normally at the top left-hand corner of any selected range.

View all Excel hints and tips