Excel Macro Help

[chris-red]

Right, I'm trying to create a Macro that will take a line of dates in the format dd/mm/yyyy and convert them to yyy/mm/dd, where yyyy - yyy is 2010 - 110, 1995 - 095 and 2150 - 250.


I do this using Text to columns twice, once / delimited for the date and once Fixed with to split the year into 2 parts.

I then find and replace on the first part of the year 19 - 0, 20 - 1 and 21 - 2.

I then concatenate into the format YYY/MM/DD, copy and paste the values and get rid of the data not require so I end up with the input dates then the output dates next to them.


This works when I record it the problem is when I run the Macro again it drops any leading 0's from the day and month cells which makes it useless

i.e.


03/02/2000 is turned into 10032 when it should be 1000302. I can;t work out what is going wrong when i do text to columns I am selecting text as the field format, any ideas?

here is the code.

10032


Sub dateconvertor()
'
' dateconvertor Macro
' Macro recorded 20/08/2010 by cibrown
'

'
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="/", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2)), _
TrailingMinusNumbers:=True
Columns("D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlFixedWidth, _
OtherChar:="/", FieldInfo:=Array(Array(0, 2), Array(2, 2)), _
TrailingMinusNumbers:=True
Selection.Replace What:="19", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="20", Replacement:="1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="21", Replacement:="2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("F:F").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1],RC[-3],RC[-4])"
Range("F1").Select
ActiveWindow.SmallScroll Down:=-6
Selection.AutoFill Destination:=Range("F1:F141")
Range("F1:F141").Select
Columns("B:E").Select
Columns("F:F").Select
Selection.Copy
Columns("G:G").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:F").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("B2").Select
ActiveWindow.SmallScroll Down:=-18
End Sub
____________________
Well, you know what they say. If you want to save the world, you have to push a few old ladies down the stairs.
Skudd:- Perhaps she just thinks you are a window licker and is being nice just in case she becomes another Jill Dando.
WANTED:- Fujinon (Fuji) M42 (Screw on) lenses, let me know if you have anything.

[Frost]

Leading zeros are not neccicary when doing mathematical operations. So if you tell a programming language, or calculator for that matter:


000000000001 + 000000000001 = 2, not 000000000002

You will either have to treat the whole thing as a 'string' that is what text is called in programming. Strings can be converted to numbers for maths, but obviously this will make them 2 instead of '02'. Most languages have a function called 'pad' which will let you put a number of 0's before to make the answer a certain length: e.g pad( text, '0', 2 ) will pad out text with 0's until it is of length 2. Thats not the correct name, but it will be very similar.

So basically convert the int's to strings.

[chris-red]