If/Then in a Macro - Changing 2 digit year into 4 digit year.

[Breanna]

Hi!!

I need a macro where I can highlight a column and change all of the 2 digit years to a 4 digit year (actually, some of the 2 digit years are only 1 digit, e.g. "9" instead of "09").

I have an if then statement that I can use in the column after it, but I'd rather change the actual numbers in the original column, rather than adding another column (and having to keep the original, too).

The statement I had was:
=if(A1>=50,1900+A1,2000+A1)

I just copy dragged down to get the cell numbers for the rest of the column... but using A1 was just for an example, here, it's not necessarily going to be in that column. It needs to be just whichever cells I select.

It seems like it should be pretty simple, but I don't know how to word it in a macro.


Starting Column Example:
[TABLE="width: 48"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]12
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]13
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]14
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]99
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]14
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]97
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[/TR]
</tbody>[/TABLE]

Ending Column Example:
[TABLE="width: 48"]
<tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]2012
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2013
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2014
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1999
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2011
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2009
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2008
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2006
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2014
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1997
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2000
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2001
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2002
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2003
[/TD]
[/TR]
</tbody>[/TABLE]


Any help would be appreciated.

Thank you!!!

Edit: for some reason I can't get rid of the borders... weird, lol.

 

[Mitrix82]

Public Sub convertYears()    
    Dim r As Range
    For Each r In Selection
        If r.Value >= 50 Then
            r.Value = 1900 + r.Value
        Else
            r.Value = 2000 + r.Value
        End If
    Next
End Sub

 

[Breanna]

Thanks Mitrix!

I'll test it out asap.

 

[hiker95]

Breanna,

Sample raw data:

Excel 2007
	A	B	C	D	E	F	G	H
1	12
2	13
3	14
4	99		12	13	14	99	11
5	11		9	8	6	14	97
6	9
7	8
8	6			0	1
9	14			2	3
10	97
11	0
12	1
13	2
14	3
15
Sheet1

Each time you select one of the colored cell groups, and, run the macro, the overall results would look like this:

Excel 2007
	A	B	C	D	E	F	G	H
1	2012
2	2013
3	2014
4	1999		2012	2013	2014	1999	2011
5	2011		2009	2008	2006	2014	1997
6	2009
7	2008
8	2006			2000	2001
9	2014			2002	2003
10	1997
11	2000
12	2001
13	2002
14	2003
15
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub UpdateYear()
' hiker95, 07/03/2014, ME789246
Dim c As Range
Application.ScreenUpdating = False
For Each c In Selection
  If c >= 50 Then
    c = 1900 + c
  Else
    c = 2000 + c
  End If
Next c
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the UpdateYear macro.

 

[Breanna]

They both work great! Thanks!!!

 

[hiker95]

Breanna,

Thanks for the macro Hiker!

Both of the ones posted work, but I'm going to use yours

Thanks for the above Private Message, and, for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[Rick Rothstein]

Here is another macro you might find interesting (it uses no loops). One reason for recommending it is that it uses the 1900/2000 century breakpoint year set in your computer's regional setting rather than picking an arbitrary 50 year breakpoint.

Sub ExpandToFourDigitYears()
  Dim Addr As String
  Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr) = Evaluate("IF(LEN(" & Addr & "),YEAR(0+(""1/1/""&" & Addr & ")),"""")")
End Sub