Prev: copy a cell from page 1 to page 2 in excel
Next: Return date of an action
From: Tommy on 27 May 2010 17:49
I'm currently working with a column of dates. Some of the dates are text and
some are numbers. The dates are in the following format 21/6/210 or
4/12/2013 (date/month/year). I used =isnumber() to verify cells. Half of
the coulmn
resulted in false values.

I'm unable to format the dates to other formats, since some are text. Is
there anyway to change the column so that all dates are numbers vs. text,
thus allowing me to format them to other date formats???
From: Tommy on 27 May 2010 17:52
Huber-

any suggestions if the date is 21/6/2010 or 6/12/2013 (day/month/year)?

"Huber57" wrote:

> Tommy,
>
> I assume the date you meant was 21/06/2010 and that the format is dd/mm/yyyy.
>
> If so, use this formula to turn it into a date:
>
> =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))
>
> This should return a number. Then format the cell as a date.
>
> HTH.
>
> "Tommy" wrote:
>
> > I'm currently working with a column of dates. Some of the dates are text and
> > some are numbers. The dates are in the following format 21/06/210
> > (mm/dd/yyyy). I used =isnumber() to verify cells. Half of the coulmn
> > resulted in false values.
> >
> > I'm unable to format the dates to other formats, since some are text. Is
> > there anyway to change the column so that all dates are numbers vs. text,
> > thus allowing me to format them to other date formats???
From: Gord Dibben on 27 May 2010 20:02
Try running them through Data>Text to Columns with column data format set to
date format of your choice.


Gord Dibben MS Excel MVP

On Thu, 27 May 2010 14:49:24 -0700, Tommy <Tommy(a)discussions.microsoft.com>
wrote:

>I'm currently working with a column of dates. Some of the dates are text and
>some are numbers. The dates are in the following format 21/6/210 or
>4/12/2013 (date/month/year). I used =isnumber() to verify cells. Half of
>the coulmn
>resulted in false values.
>
>I'm unable to format the dates to other formats, since some are text. Is
>there anyway to change the column so that all dates are numbers vs. text,
>thus allowing me to format them to other date formats???

 | 
Pages: 1
Prev: copy a cell from page 1 to page 2 in excel
Next: Return date of an action