michaelg1040
New Member
- Joined
- Jun 23, 2006
- Messages
- 44
I'm having problems with some date formats on a spreadsheet which I'm hoping you can help with. This spreadsheet is our company orderbook which is pulled from a central database and for some reason some of the dates come out as text format. I have a macro which formats the spreadsheet so that it is a bit more user friendly but I'm struggling to get all the dates in the same format.
This is what I'm doing so far using the macro - I insert a column next to the date column, then get the macro to look in the date column and convert any text dates or if a date is already in the correct format to just copy it across. It converts the text dates without any problem but for some reason it's not copying across the dates that are already in the correct format. The plan is once all the dates are in column Z I can then get rid of column Y which has all the incorrectly formatted dates.
Here is the code:
For counter = 2 To Lastrow
If Range("Y" & counter).NumberFormat = "DD/MM/YYYY" Then Range("Z" & counter).Value = _
Range("Y" & counter).Value Else Range("Z" & counter).FormulaR1C1 = "=DATEVALUE(RC[-1])"
Next counter
I'm sure I'm missing something really simple but I can't figure it out at all!
This is what I'm doing so far using the macro - I insert a column next to the date column, then get the macro to look in the date column and convert any text dates or if a date is already in the correct format to just copy it across. It converts the text dates without any problem but for some reason it's not copying across the dates that are already in the correct format. The plan is once all the dates are in column Z I can then get rid of column Y which has all the incorrectly formatted dates.
Here is the code:
For counter = 2 To Lastrow
If Range("Y" & counter).NumberFormat = "DD/MM/YYYY" Then Range("Z" & counter).Value = _
Range("Y" & counter).Value Else Range("Z" & counter).FormulaR1C1 = "=DATEVALUE(RC[-1])"
Next counter
I'm sure I'm missing something really simple but I can't figure it out at all!