We have data downloaded to Excel from our AS400 server.
However the date format is YYYYMMDD without slashes to separate.
Therefore, Excel does not recognize this as a date format and is unable to
perform calculations properly.
I'm looking for a formula or process that will make this recognized as a
date format in Excel so dates can be compared.
We are currently using Microsoft Office Excel 2003.

hi
if the dates all have the same number of characters, you might be able to
use this in a helper column added next to the data.

=MID(A2,5,2)&"/"&RIGHT(A2,2)&"/"&LEFT(A2,4)

the above assume data is in the A column. adjust if needed.
you can copy the formula and paste special values to turn them into dates
delete the as400 dates if needed.

regards
FSt1

"Judy CS" wrote:

We have data downloaded to Excel from our AS400 server.
However the date format is YYYYMMDD without slashes to separate.
Therefore, Excel does not recognize this as a date format and is unable to
perform calculations properly.
I'm looking for a formula or process that will make this recognized as a
date format in Excel so dates can be compared.
We are currently using Microsoft Office Excel 2003.

DataText to ColumnsNextNextColumn Data FormatDateYMDFinish.


Gord Dibben MS Excel MVP

On Wed, 7 May 2008 16:51:00 -0700, Judy CS <Judy
wrote:

We have data downloaded to Excel from our AS400 server.
However the date format is YYYYMMDD without slashes to separate.
Therefore, Excel does not recognize this as a date format and is unable to
perform calculations properly.
I'm looking for a formula or process that will make this recognized as a
date format in Excel so dates can be compared.
We are currently using Microsoft Office Excel 2003.

A1 = YYYYMMDD

Then

=date(left(A1,4),left(right(a1,4),2,right(a1,2))

--

Best Regards,
FARAZ A. QURESHI


"Judy CS" wrote:

We have data downloaded to Excel from our AS400 server.
However the date format is YYYYMMDD without slashes to separate.
Therefore, Excel does not recognize this as a date format and is unable to
perform calculations properly.
I'm looking for a formula or process that will make this recognized as a
date format in Excel so dates can be compared.
We are currently using Microsoft Office Excel 2003.

Sorry,

Try:

=date(left(A1,4),left(right(a1,4),2),right(a1,2))

--

Best Regards,
FARAZ A. QURESHI


"FARAZ QURESHI" wrote:

A1 = YYYYMMDD

Then

=date(left(A1,4),left(right(a1,4),2,right(a1,2))

--

Best Regards,
FARAZ A. QURESHI


"Judy CS" wrote:

We have data downloaded to Excel from our AS400 server.
However the date format is YYYYMMDD without slashes to separate.
Therefore, Excel does not recognize this as a date format and is unable to
perform calculations properly.
I'm looking for a formula or process that will make this recognized as a
date format in Excel so dates can be compared.
We are currently using Microsoft Office Excel 2003.

Dear FSt1

Yes, the input is all the same length of characters and structure. Based on
that, I figured there HAD to be a way of putting in the forward slahes for
date recognition.

So your suggestion worked great. Thanks for the assist. I'll be keeping
this for future reference and can modify if as needed.

This was a great help.
Thanks
Judy CS

"FSt1" wrote:

hi
if the dates all have the same number of characters, you might be able to
use this in a helper column added next to the data.

=MID(A2,5,2)&"/"&RIGHT(A2,2)&"/"&LEFT(A2,4)

the above assume data is in the A column. adjust if needed.
you can copy the formula and paste special values to turn them into dates
delete the as400 dates if needed.

regards
FSt1

"Judy CS" wrote:

We have data downloaded to Excel from our AS400 server.
However the date format is YYYYMMDD without slashes to separate.
Therefore, Excel does not recognize this as a date format and is unable to
perform calculations properly.
I'm looking for a formula or process that will make this recognized as a
date format in Excel so dates can be compared.
We are currently using Microsoft Office Excel 2003.

Dear Faraz

Thanks for the input. This worked too. I now have two solutions for future
reference. I'll be keeping this to refer to.

Thanks
Judy CS

"FARAZ QURESHI" wrote:

Sorry,

Try:

=date(left(A1,4),left(right(a1,4),2),right(a1,2))

--

Best Regards,
FARAZ A. QURESHI


"FARAZ QURESHI" wrote:

A1 = YYYYMMDD

Then

=date(left(A1,4),left(right(a1,4),2,right(a1,2))

--

Best Regards,
FARAZ A. QURESHI


"Judy CS" wrote:

We have data downloaded to Excel from our AS400 server.
However the date format is YYYYMMDD without slashes to separate.
Therefore, Excel does not recognize this as a date format and is unable to
perform calculations properly.
I'm looking for a formula or process that will make this recognized as a
date format in Excel so dates can be compared.
We are currently using Microsoft Office Excel 2003.

A bit late to this thread...but...

try this:

B1: =--TEXT(A1,"0000-00-00")

(format B1 as a date)

Is that something you can work with?

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Judy CS" <Judy wrote in message
...
We have data downloaded to Excel from our AS400 server.
However the date format is YYYYMMDD without slashes to separate.
Therefore, Excel does not recognize this as a date format and is unable to
perform calculations properly.
I'm looking for a formula or process that will make this recognized as a
date format in Excel so dates can be compared.
We are currently using Microsoft Office Excel 2003.