date and time formating in one cell

  • Thread starter Thread starter Kurt
  • Start date Start date
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
 
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)
 
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)
 
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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top