how do I change the format in this date?

Assuming you original data is formatted as Custom yymmdd, change the format to:
mm/dd/yyyy
--
Gary''s Student - gsnu200770


"CHARI" wrote:

> how do I change the format in this date?

"Gary''s Student" wrote:

> Assuming you original data is formatted as Custom yymmdd, change the format to:
> mm/dd/yyyy
> --
> Gary''s Student - gsnu200770
>
>
> "CHARI" wrote:
>
> > how do I change the format in this date?


No, it's not formatted that way. The original format was 1020315 and I
removed the 1 but I can't get the formatting to change. This was originally
a text file I imported. Should I import it different or can I change it as
is?

Try this formula instead:

=DATE(2000+LEFT(A1,2),--MID(A1,3,2),RIGHT(A1,2))
--
Gary''s Student - gsnu200770


"CHARI" wrote:

>
>
> "Gary''s Student" wrote:
>
> > Assuming you original data is formatted as Custom yymmdd, change the format to:
> > mm/dd/yyyy
> > --
> > Gary''s Student - gsnu200770
> >
> >
> > "CHARI" wrote:
> >
> > > how do I change the format in this date?
>
>
> No, it's not formatted that way. The original format was 1020315 and I
> removed the 1 but I can't get the formatting to change. This was originally
> a text file I imported. Should I import it different or can I change it as
> is?

"Gary''s Student" wrote:

> Try this formula instead:
>
> =DATE(2000+LEFT(A1,2),--MID(A1,3,2),RIGHT(A1,2))
> --
> Gary''s Student - gsnu200770
>
>
> "CHARI" wrote:
>
> >
> >
> > "Gary''s Student" wrote:
> >
> > > Assuming you original data is formatted as Custom yymmdd, change the format to:
> > > mm/dd/yyyy
> > > --
> > > Gary''s Student - gsnu200770
> > >
> > >
> > > "CHARI" wrote:
> > >
> > > > how do I change the format in this date?
> >
> >
> > No, it's not formatted that way. The original format was 1020315 and I
> > removed the 1 but I can't get the formatting to change. This was originally
> > a text file I imported. Should I import it different or can I change it as
> > is?

I got back 08/15/2011 ..... not 03/15/02

Is the date supposed to be March 15, 2002?

Cliff Edwards

If the date format from your database is cyymmdd, here is the formula
to convert it to a value that Excel will recognize as the equivalent
date:

=IF(LEN(A1)=7,VALUE(CONCATENATE(MID(A1,4,2),"/",RIGHT(A1,2),"/",MID(A1,2,2))),VALUE(CONCATENATE(MID(A1,3,2),"/",RIGHT(A1,2),"/",LEFT(A1,2))))

The formula also takes into account whether the date format prior to
2000 was mmddyy and converts to date serial.

You can format the date serial in any way you want.

Cliff Edwards

"ward376" wrote:

> Is the date supposed to be March 15, 2002?
>
> Cliff Edwards
>
> yes,

"ward376" wrote:

> If the date format from your database is cyymmdd, here is the formula
> to convert it to a value that Excel will recognize as the equivalent
> date:
>
> =IF(LEN(A1)=7,VALUE(CONCATENATE(MID(A1,4,2),"/",RIGHT(A1,2),"/",MID(A1,2,2))),VALUE(CONCATENATE(MID(A1,3,2),"/",RIGHT(A1,2),"/",LEFT(A1,2))))
>
> The formula also takes into account whether the date format prior to
> 2000 was mmddyy and converts to date serial.
>
> You can format the date serial in any way you want.
>
> Cliff Edwards
>

PERFECT, THANK YOU!!