date and time formating in one cell

K

Kurt

I have the following date/time stamps in one cell:

20031229152357
20031230150834
20031230150834
etc...

As you can tell, the date is yyyy\m\d and time is h:mm:ss

The problem I have is the numbers appear exactly as above.

My question is how do I get the respective date and time
to show up in the cells?

Right now, I am only getting the "##############" to show
in the cells.

Thanks for your help,
Kurt
 
B

Bob Phillips

Kurt,

Are you not inputting this data as time, that is
29/12/2003

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

Kurt,

Are you not inputting this data as time, that is
29/12/2003 15:23:57
etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
N

Norman Harker

Hi Kurt!

This is ISO8601:2000 approved non separated date and time format

You need to parse to get a date time:

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2
),RIGHT(A1,2))
Format (e.g.) yyyy-mm-dd hh:mm:ss

For my format I've chosen the ISO8601:2000 approved separated format
that is recognised (AFAIK) by all versions of Excel.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Top