Date Format Problem

[babarorhum]

Hello,

I am having an interesting issue with the cell data formatting. In the same column, i have dates being displayed in two formats:

• format 1, e.g. 3/13/2008 (displayed on the left)
• format 2, e.g. 2.12.2008 (displayed on the right)

The format 2 is a date format for sure. However, the format 1 appears to be in general format. When i try to convert from general to date format such as format 2. it does not work, it does show the format is now a date format, but data is still displayed on the left and with its original display (ex. 3/13/2008).
Of course by typing manually the date it works, but i have hundreds of lines in a similar state.

does someone know how to change format 1 cell into dates without having to update them manually ?
Many thanks in advance !
Antony

[arthurbr]

Hi, try the following:
select your range
Select Data - Text to Columns
Select Fixed width - Next - Next
Under column data format check "date" and select the date format you need ( depending on your regional settings you may have to try a couple of possibilities)
That should do the trick

[mudraker]

Try this
select column or range of dates
Data Menu
Text To Columns
Delimited Option (can also use Fixed Width)
Next Button
Remove all ticks from Delimiters Check Boxes
Next Button
Select Date from Column Data Format option
Select date format to suit (DMY)
Finish Button

Oops beaten by arthurbr

[babarorhum]

Hi !
Thanks both of you for your answers.
However, it does not seem to work in this case. The format does change to date but the data is still displayed as mm/dd/yyyy and on the left side of the column.
Would you have more suggestions ?

Antony

[JBeaucaire]

Not sure what you may be doing differently since this is the proper method.

Try running this macro on the sheet where the dates reside. It presumes the data is in the B column starting at cell B2. Adjust the macro to point to the first cell.

Also, you will have to highlight all the date cells before activating the macro:

Sub DateConvert()
    Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True
End Sub

This does result i n all dates right-flush in the proper format.

[babarorhum]

Hi !
I've changed the date format to MDY in the last step and it works like a charm ! I thought the last option dealt with the output format but it was the input format (format#1 in the example).
Thanks everyone for the help!

Antony

[arthurbr]

Avec plaisir !