Automatically convert Text with 2-digit year
 

Hi, I'm obtaining dates from another column using =left(a2,8). (This column
contains text following a date.) After I copy and paste special so that the
values are displayed and not the formula, some dates are displayed as "Text
with 2-digit year." This format does not allow these cells to be included
when I filter. I know that I can convert each one manually, by clicking into
each cell, or using "Convert XX to 20XX" but with the amount of cells that
I'm working with, this will take forever! Is there an easier way to do this?

Answer: Automatically convert Text with 2-digit year
 

Yes, there is an easier way to convert the "Text with 2-digit year" format to a date format that can be filtered. You can use the Text to Columns feature in Excel to convert the text to dates.

Here are the steps to follow:

1. Select the cells that contain the "Text with 2-digit year" format.
2. Click on the Data tab in the ribbon.
3. Click on the Text to Columns button.
4. In the Convert Text to Columns Wizard, select the Delimited option and click Next.
5. In the next step, make sure that only the "Space" option is selected as a delimiter and click Next.
6. In the next step, select the column that contains the date and choose the "Date" option under Column data format.
7. Select the date format that matches the format of your dates (e.g. MDY for month-day-year format) and click Finish.

Excel will then convert the "Text with 2-digit year" format to a date format that can be filtered. You can now use the filter function to filter the dates as needed.

Automatically convert Text with 2-digit year
 

If you make it:

=1*LEFT(A2,8)

and you have valid date strings, then this will convert the string
into Excel date formats, so you can use Format | Cells | Number tab to
display the date with 4 digit years (eg mm/dd/yyyy in the Custom box).

Hope this helps.

Pete

On Nov 16, 12:58 am, juniper810
wrote:
Hi, I'm obtaining dates from another column using =left(a2,8). (This column
contains text following a date.) After I copy and paste special so that the
values are displayed and not the formula, some dates are displayed as "Text
with 2-digit year." This format does not allow these cells to be included
when I filter. I know that I can convert each one manually, by clicking into
each cell, or using "Convert XX to 20XX" but with the amount of cells that
I'm working with, this will take forever! Is there an easier way to do this?

Automatically convert Text with 2-digit year
 

Hi

Try
=TEXT(LEFT(A1,8),"dd mm yyyy")

--
Regards
Roger Govier



"juniper810" wrote in message
...
Hi, I'm obtaining dates from another column using =left(a2,8). (This
column
contains text following a date.) After I copy and paste special so that
the
values are displayed and not the formula, some dates are displayed as
"Text
with 2-digit year." This format does not allow these cells to be included
when I filter. I know that I can convert each one manually, by clicking
into
each cell, or using "Convert XX to 20XX" but with the amount of cells that
I'm working with, this will take forever! Is there an easier way to do
this?