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

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

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:

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:

=INT(B3)

TRUNC Function

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

=TRUNC(B3)

ROUNDDOWN Function

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

=ROUNDDOWN(B3,0)

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:

=B3-INT(B3)

Adjust Formatting

Last, go to the Home Ribbon > Number and change the cell formatting from General to Short Date (for the date) and Time (for the time):

Final Result Showing as below.

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:

=B3

=B3

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.

You can change the date format from the Cell Formatting Menu:

Type “m/dd/yyyy” in the Type area to set date formatting, or “h:mm AM/PM” to set time formatting..

To access the Cell Formatting Menu use shortcut CTRL + 1 or press this button:

TEXT Function

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

=TEXT(B3,"m/d/yyyy")

=TEXT(B3,"hh:mm:ss AM/PM")

However, 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.