Date format & Text to columns Macro

[louie420]

I run many queries. The date format of these queries as they come out of our database is always YYYY-MM-DD. They need to be formatted to MM/DD/YY. I would like to be able to change the format with one macro that can be used in any selected column. Please help me. Thanks.

 

[PaddyD]

On 2002-09-17 17:44, louie420 wrote:
I run many queries. The date format of these queries as they come out of our database is always YYYY-MM-DD. They need to be formatted to MM/DD/YY. I would like to be able to change the format with one macro that can be used in any selected column. Please help me. Thanks.

Hi - welcome to the board!

When you say 'formatted' - do you mean that you have successfully inported data that is now being held as a date value (i.e. a number) & you want to change the way it is represented ('formatted'), or do you mean that you've imported some 'date' fields but they are now being held as text & you now need to change this to an appropriately formatted date value? What does =isnumber() on one of the cells with a 'date' in it return?

Paddy

 

[louie420]

On 2002-09-17 18:28, PaddyD wrote:

On 2002-09-17 17:44, louie420 wrote:
I run many queries. The date format of these queries as they come out of our database is always YYYY-MM-DD. They need to be formatted to MM/DD/YY. I would like to be able to change the format with one macro that can be used in any selected column. Please help me. Thanks.

Hi - welcome to the board!

When you say 'formatted' - do you mean that you have successfully inported data that is now being held as a date value (i.e. a number) & you want to change the way it is represented ('formatted'), or do you mean that you've imported some 'date' fields but they are now being held as text & you now need to change this to an appropriately formatted date value? What does =isnumber() on one of the cells with a 'date' in it return?

Paddy

I suppose I've imported some 'date' fields but they are now being held as text & I now need to change this to an appropriately formatted date value? The =isnumber() returns "FALSE".
THANKS.

 

[PaddyD]

2 options:

1) with a formula:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

2) with a 'trick':
- highlight the column of text 'dates'
- go to Data | Text to columns
- click next twice
- select date & the YMD option from the drop down
- click finish

Recording these sets gives:<pre>

Sub dates()
'
' dates Macro
' Macro recorded 19/09/2002 by Paddy
'

'
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 5)
End Sub</pre>

which could be cleaned up a bit.

Paddy
This message was edited by PaddyD on 2002-09-18 14:20

 

[louie420]

On 2002-09-18 14:13, PaddyD wrote:
2 options:

1) with a formula:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

2) with a 'trick':
- highlight the column of text 'dates'
- go to Data | Text to columns
- click next twice
- select date & the YMD option from the drop down
- click finish

Recording these sets gives:<pre>

Sub dates()
'
' dates Macro
' Macro recorded 19/09/2002 by Paddy
'

'
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 5)
End Sub</pre>

which could be cleaned up a bit.

Paddy
This message was edited by PaddyD on 2002-09-18 14:20

Paddy,

I did use the record function you mentioned however, the Destination range is still literal. I need it to be variable dependent on what column the date format happens to be in.

Louie.