Split Excel date and time in two 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 French to the “Date-Heure” 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.

How to split Excel date and time in two columns using formulas?

First create the two Excel destination columns for the date and time.

1. Prepare the Date only column to be separated from the date time column

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 column.

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

Split one date time column into a date column using the INT Excel function.
Split date using the INT Excel function.

2. Prepare the Time Only column to separate 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 Date column.
Split an Excel Data time column and isolate the time using the Modulo function.
Isolate the time using the Excel Modulo function

3. The result with the original Excel column, after the Split from the Excel date and time in two 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.

Finally, to go even further it is interesting to see how to calculate the differences up to seconds from two dates. For instance, calculate the difference with a Date, Time and Seconds format, here is an example in SQL language.

Be the first to comment

Leave a Reply

Your email address will not be published.