Split Date and Time

If you have in a cell with date and time (like the use of the NOW function) it's easy to split them with these 2 techniques.

How to split the date with formulas?

Let's start from the following document where you have in column A Date & Time

We want to split

• The date in column B
• And the time in column C

To perform this separation, we must keep in mind that 1 represents 1 day.

Time is a fraction of a day, for example, 0.33333 is 8 hours (1/3 of a day). Have a look at this article about the Date and Time format.

Step 1: Extract only the date

So, to extract the date, you just have insert your value in column A in the INT function.

=INT(A2)

INT means integer, so you extract the whole part of the cell in column A. And the whole number is the date (the decimal part is the time).

It is possible that you need to change the format of the column B to display the format 'Date'.

Step 2: Extract the time

To extract the time, simply subtract the data in column A with the entire value calculated in column B.

=A2-B2

Again, it is possible that you have to change the format of the column C to display the cell format to 'Time'

And the final result is

How to split a date with Power Query?

With Power Query, the techniques is more simple. Let's start with these dates embedded in a Table.

Step 1: Load your dates to Power Query

Once your data are in a Table, you can load them to Power Query with the menu Data > From Table / Range

And then, in Power Query, you can visualise your data in Power Query

Step 2: Change the type of your data

As you can see on the header, you have the symbol ABC/123. That means Power Query consider your data as "Any" ; not a text, not a date, not a number.

It's compulsory to change the type of your data to Date & Time

And then, the type of the data of your column is now "Date/Time" and the pictogram isn't the same

Step 3: Extract Date

Once the type of your data is Date and Time, it's very easy to extract the Date.

1. Tab Add Column
2. Then Date
3. And finally Date Only

And the result is amazing; a new column with only the date

Step 4: Extract Time

Here, the technique is as simple as the previous step

1. Select the column with Date and Time first
2. Then, select the tab Add Column
3. And Sub-Menu Time
4. Finally, Time Only

And now, you have another column with the Time only

Related posts