98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VBA - separating month and year from date, inserting two c
Excel VBA - separating month and year from date, inserting two c
Resolved · High Priority · Version 2010
Tue 19 Aug 2014, 17:31Delegate 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:53Trainer 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:58Delegate 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:28Trainer 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:37Delegate 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 information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Return to the active cell after scrollingWhen 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. |
