In Excel, if you have a cell with date and time (like the NOW function), you can easily split it into two different cells with only two formulas.
- The INT function to extract the date
- Then a subtraction to extract the time.
Your cells have a Date and Time
Let's start from the following document where you have in column A Date & Time.
To display in the same cell Date & Time, we have used this custom date format.
We want to split
- The date in column B
- And the time in column C
Remember that in Excel
- The value 1 is always a day
- The duration (the time) is a fraction of a day (between 0 and 1)
Step 1: Extract only the date
So, to extract the date, you just have to 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). You may need to change the format of column B to display 'Date'.
Step 2: Extract the time
To obtain the time, subtract the data in column A from the total value computed in column B.
Again, it is possible that you have to change the format of column C to display the cell format to 'Time.'
Extract only the Time
This formula extracts only the Time without extracting the date.
Split Date and Time with Power Query
Have you heard about Power Query? It's a tool that transforms your data without a formula in Excel. This step-by-step guide explains how to split date and time with Power Query.
21/01/2022 @ 14:59
Can you please tell me how to split Dec 25, 2019 - 11:17 PM into date in separate column?
17/07/2021 @ 11:36
how to split Date from a sentence in excel
19/08/2021 @ 07:05
You must use FlashFill to extract your date from your sentence
02/07/2021 @ 18:48
How to convert 07/21/2020 4:30PM to 07/21/2020 16:30 in excel
02/07/2021 @ 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)
23/06/2021 @ 07:31
DBCPL/2141/20-21-08/03/2021 how seperate the date in this case
18/02/2021 @ 05:21
Thanks bro that's really easy
28/06/2020 @ 13:44
Does not work for me. I get a VALUE error
28/06/2020 @ 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)
12/06/2020 @ 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
09/01/2020 @ 05:09
If you want the time to be separated, use -
=TIME(HOUR(A2),MINUTE(A2), SECOND(A2))
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
30/12/2019 @ 09:42
13-11-2019 09:04:37
help me split this into date and time separately please
03/01/2020 @ 06:25
That's what the article describe
12/01/2021 @ 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.
--RK
13/01/2021 @ 19:24
It should, or in your cell you have Text and not a date
24/09/2019 @ 08:32
Thanks a lot Buddy 🙂
28/08/2019 @ 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
28/08/2019 @ 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.
06/07/2019 @ 10:23
The column with dates and time, has text and number . Tried INT unsuccessfully
Contract Creation Date
3/10/2018
5/10/2018
29/01/2018 11:06:47
31/01/2018 11:06:28
10/7/2017
20/12/2018 12:33:33
31/05/2018 15:30:17
17/09/2018 12:13:19
12/2/2018
12/3/2018
26/02/2018 13:10:03
29/03/2017 16:12:13
5/8/2017
11/8/2017
31/01/2018 17:15:02
25/01/2019 @ 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
26/01/2019 @ 10:16
No easy to reply without the file.
Can you send me few rows in an excel file to contact@excelexerc2stg.wpenginepowered.com
05/12/2018 @ 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.
27/06/2018 @ 09:58
First time i got this information, its saved my lot of times
02/06/2018 @ 14:03
Thank you so much for this information, you really saved me some time.
08/02/2018 @ 20:00
YOU ARE THE BEST, THANK YOU FOR THIS INFORMATION!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!