Extracting date from a cell

[Steven M. Britton]

I have an excel file that export from my UPS computer,
when is export the date is recorded as 20030702170352.
Which is the year/month/date/hour/minute/second...

What I need to do is move the year, month and day into
there own columns so that I can use concatenate to bring
them together to read 07/02/2003...

What is the function that will extract the data and load
it into its own columns? Any Ideas?

-Steven M. Britton

 

[dvt]

I have an excel file that export from my UPS computer,
when is export the date is recorded as 20030702170352.
Which is the year/month/date/hour/minute/second...

What I need to do is move the year, month and day into
there own columns so that I can use concatenate to bring
them together to read 07/02/2003...

I'd start with the LEFT, MID, and/or RIGHT functions. For example,
=LEFT("20030702170352",4) gives 2003 as the result. Put a few of these
functions together and you can produce the date in your desired format.

Dave
dvt at psu dot edu

 

[Ron Rosenfeld]

I have an excel file that export from my UPS computer,
when is export the date is recorded as 20030702170352.
Which is the year/month/date/hour/minute/second...

What I need to do is move the year, month and day into
there own columns so that I can use concatenate to bring
them together to read 07/02/2003...

What is the function that will extract the data and load
it into its own columns? Any Ideas?

-Steven M. Britton

=DATE(INT(A1/10^10),MOD(INT(A1/10^8),100),MOD(INT(A1/10^6),100))

will turn it into a date serial number that excel can recognize. Then just
format it how you like: mm/dd/yyyy


--ron

 

[Steven M. Britton]

-----Original Message-----

(A1/10^6),100))

will turn it into a date serial number that excel can recognize. Then just
format it how you like: mm/dd/yyyy


--ron
.

Ron,

Thanks that is excatly what I needed...

 

[Aladin Akyurek]

Another...

=--TEXT(LEFT(A1,8),"0000-00-00")