How to Seperate Date and Time in Excel

Sometimes when you receive data in an Excel file that has been downloaded from a database the date and time are in the same column together (as shown in column A in the image).  So it could show as 01/08/2010 11:50.  This is ok on its own, but what if you want to seperate date and time excel date time formatand have each occupy it’s own cell respectively.  For a small amount of data you could perhaps go down the column and type the date into a seperate column and the same for the time value.  This method is slow, but will get the work done – but what if you have hundreds or thousands of lines of data to go through…!?  Easy answer…. there are two Excel functions that can be used to split them!

Seperate Date in Excel

I will assume your date and time data are in column A and starts at A1 as shown in the image.  So, click in cell B1 and type the following =INT(A1) and hit enter.  The cell will now display the date but with zeros displaying for the time element.  No drag this formula down the column.  This can be done by hovering the mouse over the bottom right hand corner of cell B1 until it turns to cross hairs.  Then either click and drag, or double click.  Copy the now highlighted cells and then select cell B1 again and select paste special, and then paste the values.  This will remove the function just written and replace it with the value – our now seperated date.  Then format the cells by selecting Format > Cells and then in the pop up box, click on the Number tab and select Date and click OK.

Seperate Time in Excel

Again I will assume your data is column A as before.  This time in column C1 type the following =MOD(A1,1) and hit enter.  The cell will now display a date in 1900 and then the time.  As before copy the function down the column, and while the cells are highlighted, copy them, and then select paste special and click values.  This will then paste the time values into column C.  Now just to format the time, again same procedure as the Date process.  Select Format > Cells and then in pop up box select the Number tab and then select Time and click OK.

Simple, huh?  The screencast above should also help if you get stuck!  So to reiterate, to seperate date type =INT(Cell) and to seperate time type =MOD(Cell,1).  The Cell represents the cell where you first date time is located, so could be A1, B1, G5 or any othe cell.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>