Formatting Dates from Text to Numbers
 

I receive data daily with dates in the following formats: dd/mm/yyyy,
d/m/yyyy,d/mm/yyyyy, or dd/m/yyyy... The values actually are text vs.
numbers.

Regardless I use text to columns, to separate the values into three columns.
Then I use =date(year, month, day), actually =date(a2,b2,c2) to populate the
date in one field. Does anyone have any suggestions as to how to do this
better or faster?

Thanks,

Tommy

Formatting Dates from Text to Numbers
 

You can use the Text to Columns Wizard to convert the dates

--Select the range of dates which needs to be corrected.

--From menu Data'Text to Columns' will populate the 'Convert Text to Columns
Wizard'.

--Hit NextNext will take you to Step 3 of 3 of the Wizard.

--From 'Column Data format' select 'Date' and select the date format in which
your data is ('DMY')

--Hit Finish. MSExcel will now convert the dates to the default date format
of your computer.



"Tommy" wrote:

I receive data daily with dates in the following formats: dd/mm/yyyy,
d/m/yyyy,d/mm/yyyyy, or dd/m/yyyy... The values actually are text vs.
numbers.

Regardless I use text to columns, to separate the values into three columns.
Then I use =date(year, month, day), actually =date(a2,b2,c2) to populate the
date in one field. Does anyone have any suggestions as to how to do this
better or faster?

Thanks,

Tommy

Formatting Dates from Text to Numbers
 

Suppose you have selected a column with dates...in Step 2 dont select any
delimiters...or from Step1 select that as Fixed Width..

"Jackpot" wrote:

You can use the Text to Columns Wizard to convert the dates

--Select the range of dates which needs to be corrected.

--From menu Data'Text to Columns' will populate the 'Convert Text to Columns
Wizard'.

--Hit NextNext will take you to Step 3 of 3 of the Wizard.

--From 'Column Data format' select 'Date' and select the date format in which
your data is ('DMY')

--Hit Finish. MSExcel will now convert the dates to the default date format
of your computer.



"Tommy" wrote:

I receive data daily with dates in the following formats: dd/mm/yyyy,
d/m/yyyy,d/mm/yyyyy, or dd/m/yyyy... The values actually are text vs.
numbers.

Regardless I use text to columns, to separate the values into three columns.
Then I use =date(year, month, day), actually =date(a2,b2,c2) to populate the
date in one field. Does anyone have any suggestions as to how to do this
better or faster?

Thanks,

Tommy

Suppose column A has all the text dates and supposing that the cells have trailing spaces, you can use: =TRIM(A1)*1. Then format the cell into MM/DD/YYYY

or =TEXT(A1,"MM/DD/YYYY")*1