Download the example workbook
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.
First we will use the INT Function to return the integer value representing the date:
Or we can use the TRUNC Function to trim off the decimal (time) value:
Or you can use the ROUNDDOWN Function to round down to the nearest whole integer (date):
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:
Easy Excel Automation
Add Excel automation to your workbook with just a few clicks.
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:
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:
You can also use the TEXT Function in separate cells to store the date and time values separately as text:
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.