Excel 2008 : Date format

**ScottL** · 03-31-2011, 09:38 AM

Hi all,
I have inherited a data set with some important historical data that I need to work with. The issue I am having is that the date and time is saved as one cell with just the number 200810151200 for 2008 october 15 1200. It is a huge data set with values generated each 15 minutes all year. I would like to clean this up into a column with the date (formatted day/month/year) and a second column with the time. I have tried formatting the cell as a date with various date formats to no avail. is there any quick way to extract the values for columns or do I have to go through and type everything out?

**Domski** · 03-31-2011, 09:51 AM

Hi,

Try this:

For the date: =DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))
For the time: =TIME(MID(A1,9,2),RIGHT(A1,2),0)

Dom

**ScottL** · 03-31-2011, 10:47 AM

Works perfectly. Thank you for the prompt and excellent response.

**ScottL** · 04-11-2011, 09:03 AM

Similar question. Now the date is save as 12/1/2007 12:00:00 AM formatted as date and time cell. If i change the format to general number it is 37955. What is the best way to separate the column into one column for date and one for time?

**Domski** · 04-11-2011, 09:09 AM

If A1 contains a serial date/time value then:

=INT(A1) will give you the date
=MOD(A1,1) will give you the time

Format the cells as appropriate.

Dom