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:   examples should how dates are stored as serial numbers and times stored as decimal values  = .5 = 12pm, etc.)>>

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

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

<<show picture – same as previous section picture except date should have short date formatting, and time should have time formatting>>

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:

<<show picture with formatting = m/dd/yyyy>>

<<show picture with formatting = h:mm AM/PM>>

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:

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

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!