Prev: Sum a range based on a column
Next: Pick list width changes by itself...why?
From: Tommy on 4 Jun 2010 09:41
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
From: Jackpot on 4 Jun 2010 09:45
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 Next>Next 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
From: Jackpot on 4 Jun 2010 09:53
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 Next>Next 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
 | 
Pages: 1
Prev: Sum a range based on a column
Next: Pick list width changes by itself...why?