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

Download Example Workbook

Download the example workbook

This tutorial will demonstrate multiple 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:

Value of Date and Time

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)

Split INTTRUNC Function

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

=TRUNC(B3)

Split Trunc Date

ROUNDDOWN Function

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

=ROUNDDOWN(B3,0)

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:

=B3-INT(B3)

Time value

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):

Split Date and Time

Final Result Showing as below.

Time Value With correct

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

Date Format

=B3

Time Format

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:

custom number formats excel

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:

number format 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")

Date Text

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

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