Date Power Query

Split Date and Time

Reading Time: 3 minutes

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


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

Menu Data From Table Range

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

Load data to Power Query from a Table

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.

Type Any in Power Query

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

Power Query Change Type to Date and Time

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

Type Date and Time

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
Extract Date from Date and Time

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

Date only extracted

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
Power Query Extract Time only

And now, you have another column with the Time only

Time extracted only

Related posts

Change the default Date format in Excel

Frédéric LE GUEN

Time Format in Excel

Frédéric LE GUEN

Date Format in Excel

Frédéric LE GUEN


shiva 17/07/2021 at 11:36

how to split Date from a sentence in excel

Frédéric LE GUEN 19/08/2021 at 07:05

You must use FlashFill to extract your date from your sentence

Riyas 02/07/2021 at 18:48

How to convert 07/21/2020 4:30PM to 07/21/2020 16:30 in excel

Frédéric LE GUEN 02/07/2021 at 19:29

Hi, if the contain of your cells is already in time format (mm/dd/yyyy hh:mm AM/PM) by (mm/dd/yyyy hh:mm)

Dhananjay 23/06/2021 at 07:31

DBCPL/2141/20-21-08/03/2021 how seperate the date in this case

Tu Le 18/02/2021 at 05:21

Thanks bro that's really easy

Roderic 28/06/2020 at 13:44

Does not work for me. I get a VALUE error

Frédéric LE GUEN 28/06/2020 at 14:56

It's certainly because the content of your cell is not a date.
You can check that with this logical test = ISNUMBER(A1)

Jill 12/06/2020 at 13:22

Hey I want to seperate * 02/02/2020 03:00 AM - 03:59 AM this format into 2 seperate column date and time

Prakash 09/01/2020 at 05:09

If you want the time to be separated, use -

This will separate the time. and then for date, copy the date-time column and paste it, and change the format to only show the date

Selvi 30/12/2019 at 09:42

13-11-2019 09:04:37
help me split this into date and time separately please

Frédéric LE GUEN 03/01/2020 at 06:25

That's what the article describe

Roo 12/01/2021 at 14:30

it wont work. its eastern date format. when the day value is less than or equal to 12 this method works in dd-mm-yyyy format. We need a work around guys.

Frédéric LE GUEN 13/01/2021 at 19:24

It should, or in your cell you have Text and not a date

naseer khan 24/09/2019 at 08:32

Thanks a lot Buddy 🙂

gloshan kumar 28/08/2019 at 14:14

hi i am getting error ( #VALUE!) while applying INT

Sample given below

Actual Pickup Time
06-19-2017 22:39
06-12-2017 20:22

Frédéric LE GUEN 28/08/2019 at 14:26

It's because the contained of the cells are text and not date. Try one of your date +1 ; you will return an error too.

Kay 06/07/2019 at 10:23

The column with dates and time, has text and number . Tried INT unsuccessfully

Contract Creation Date
29/01/2018 11:06:47
31/01/2018 11:06:28
20/12/2018 12:33:33
31/05/2018 15:30:17
17/09/2018 12:13:19
26/02/2018 13:10:03
29/03/2017 16:12:13
31/01/2018 17:15:02

Joe 25/01/2019 at 23:35

Hi there,

I tried doing this with date/time cells in this format, 2018-11-15 17:09:42 PST, and all I get is #VALUE. I changed the cell from text to number, and nothing changed. I have 2600+ cells to change, and don't relish having to do it one-by-one. Any help would be greatly appreciated. Thanks

Frédéric LE GUEN 26/01/2019 at 10:16

No easy to reply without the file.
Can you send me few rows in an excel file to

Demi 05/12/2018 at 03:27

I simply wanted to thank you so much all over again.
I do not know the things that I could possibly have sorted
out without the opinions discussed by you concerning such field.
It absolutely was an absolute scary problem in my circumstances, but
being able to see the very well-written avenue you dealt with it forced me to
weep for contentment. I am happy for this support and thus hope that you know what a powerful job you were doing educating the others via your webpage.
Probably you haven't met all of us.

Friend 27/06/2018 at 09:58

First time i got this information, its saved my lot of times

Jose 02/06/2018 at 14:03

Thank you so much for this information, you really saved me some time.

Ivan 08/02/2018 at 20:00

YOU ARE THE BEST, THANK YOU FOR THIS INFORMATION!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


Leave a Comment