How to split date and time in Excel into separate columns?

Excel tutorial for separating the date and time into separate columns.

How to split date and time in two columns with Excel? Splitting a date from the time with Excel is not as simple as that. We will see how to do it with a practical example in Excel. Indeed, often when working with Excel, the Dates columns contain in practice the “Timestamp” format which corresponds in English to the “Date-Time” format.

However, to perform calculations on hours or days it is more practical to split the dates and times. For example, to find a time difference between 2 schedules it is easier to manipulate the hours alone. There are many Excel formulas to handle dates and extract each element like YEAR(), MONTH(), DAY() for example. First create the two Excel destination columns for the date and time.

1. Prepare the Date only column to extract from date time

First, fill in the Date column, “Date only” in the example:

1. Enter the formula =ENT(A2)
2. Assign the Date Format to the Date only column.

The Excel function INT() means Integer, to be applied to your first cell, here in the A2 cell, containing the Date and Time to be separated.

2. Prepare the Time Only column to split from the date time

Secondly, to prepare the “Time only” column in our Excel file, use the Excel MOD() function, for Modulo in mathematics:

1. Enter the formula =MOD(A2;1)
2. Assign the Time Format to the Time only column.

3. Result with Excel date and time in two separate columns

Finally, observe the result with the two columns Date only and Time only perfectly separated in Excel with respectively a Date format and a Time format.

Conclusion on isolating time and date in formulas

Finally, this tutorial explains step by step how to separate date and time with Excel formulas and assign the correct display format. To go even further, it’s interesting to see how to calculate differences down to seconds from two dates in T-SQL this time, using a Date / Time / Seconds format. Here’s an example in SQL language. More information on the MOD function page on the Microsoft site.