Split Date & Time into Separate Cells – Excel & Google Sheets

This tutorial will demonstrate the different ways to split dates and times into separate cells.

Split Date Time Main

Dates and Times in Excel

In Excel, dates are stored as serial numbers where each whole number represents a unique date. Times are stored as decimal values. Look at this example:

 

<<steve update:   ##### represents m/d/yyyy and .5 represents 12pm – halfway through the day>>

Knowing how dates and times are stored in Excel, makes it easy to split them into separate cells.

Split Dates and Times

To split dates and times, there are several functions that we can use to return the integer value of the date and time.

INT Function

First we will use the INT Function to return the integer value representing the date:

Split INTTRUNC Function

Or we can use the TRUNC Function to trim off the decimal (time) value:

Split Trunc Date

ROUNDDOWN Function

Or you can use the ROUNDDOWN Function to round down to the nearest whole integer (date):

Split Round Down

Time Value

Next we will subtract the integer (calculated using one of the above 3 methods) from the original date and time. The remainder will be the time:

Time value

Split Date & Time With Formatting

You can also split the date and time into two separate cells by referencing the original cells and adjusting the formatting:

Date

Time

However, when you adjust the formatting to show only the date and/or time, the original entire date & time remains in the cell as a serial number with a decimal.  The only difference is the final display to the user.

Read our article on Date Formatting to learn more.

TEXT Function

You can also use the TEXT Function in separate cells to store the date and time values separately as text:

 

Date Text

Time TextHowever, these dates and times are now stored as text and the usual date and time math will no longer apply.

Split Date & Time into Separate Cells in Google Sheets

All of the above examples work exactly the same in Google Sheets as in Excel.

Split Date Time Google