Question description:
This user has given permission to use the problem statement for this
blog.
I have a column containing date and time data together and i need to isolate the time data. When I tried to use text to column, it changed all the times to AM. There is an extremely large amount of data - over 550,000 rows so manually changing it won't work. The file is too large to attach.
Solved by S. E. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
07/06/2018 - 02:17
Hi
Excelchat Expert
07/06/2018 - 02:17
Welcome to Got It Pro.
User
07/06/2018 - 02:17
hello
Excelchat Expert
07/06/2018 - 02:18
Please use date value function to extract the data from the text and format it accordingly.
Excelchat Expert
07/06/2018 - 02:18
Please share the sample in preview.
Excelchat Expert
07/06/2018 - 02:21
Hi, there?
User
07/06/2018 - 02:23
hi
User
07/06/2018 - 02:23
sorry i was trying to c/p the data but its too much
User
07/06/2018 - 02:23
froze up
Excelchat Expert
07/06/2018 - 02:23
Ok.
User
07/06/2018 - 02:23
just waiting for my spreadsheet to come back to life
Excelchat Expert
07/06/2018 - 02:23
Please try to add one or 2 lines.
Excelchat Expert
07/06/2018 - 02:24
in the preview or use datevalue function to get the date value and convert to the required format.
Excelchat Expert
07/06/2018 - 02:25
Please review the formula.
Excelchat Expert
07/06/2018 - 02:25
I have changed the format to date.
User
07/06/2018 - 02:26
but i need the time
Excelchat Expert
07/06/2018 - 02:26
You are looking for date only?
User
07/06/2018 - 02:26
time
User
07/06/2018 - 02:27
i added some pm values for reference
User
07/06/2018 - 02:27
i tried to separate using text to columns but it changed all the times to AM
Excelchat Expert
07/06/2018 - 02:27
Ok. I am working on it.
Excelchat Expert
07/06/2018 - 02:28
You can use timevalue function for this.
User
07/06/2018 - 02:28
hmm
User
07/06/2018 - 02:28
it didn't work for me
User
07/06/2018 - 02:28
i got #VALUE!
Excelchat Expert
07/06/2018 - 02:29
Can you please check in the preview, how it was applied?
User
07/06/2018 - 02:29
i did the same
User
07/06/2018 - 02:30
=timevalue(B2)
User
07/06/2018 - 02:30
the cell says: #VALUE!
Excelchat Expert
07/06/2018 - 02:30
So, there could be problem with the date format in the cell.
Excelchat Expert
07/06/2018 - 02:30
So, it is not reading the date in excel.
Excelchat Expert
07/06/2018 - 02:30
Please send me a sample document with one row.
Excelchat Expert
07/06/2018 - 02:30
I will check here.
User
07/06/2018 - 02:31
format on my end is custom - m/d/yyyy h:mm
Excelchat Expert
07/06/2018 - 02:32
it may show like that. But, it may not be a date, when you are downloading the data from third platforms.
Excelchat Expert
07/06/2018 - 02:32
Please share sample.
User
07/06/2018 - 02:32
not sure what you mean
User
07/06/2018 - 02:32
the data i c/p is from the column i am using
Excelchat Expert
07/06/2018 - 02:33
Please share a single row of data into the chat.
Excelchat Expert
07/06/2018 - 02:33
Please upload the document using paper clip icon.
Excelchat Expert
07/06/2018 - 02:33
Google sheet can adjust the dates and times correctly.
User
07/06/2018 - 02:33
but i am using excel
Excelchat Expert
07/06/2018 - 02:34
But, excel can't do that. So, i need to see the problem.
User
07/06/2018 - 02:35
i see
Excelchat Expert
07/06/2018 - 02:35
So, i want to check it once.
User
07/06/2018 - 02:36
ok i added it
User
07/06/2018 - 02:36
row 16
Excelchat Expert
07/06/2018 - 02:37
Here it shows zero
Excelchat Expert
07/06/2018 - 02:37
I need your excel to check.
User
07/06/2018 - 02:37
it just had #VALUE! there
User
07/06/2018 - 02:37
but ok
User
07/06/2018 - 02:37
what do you propose
User
07/06/2018 - 02:38
the value should be 12:00:00 pm
User
07/06/2018 - 02:38
or am
Excelchat Expert
07/06/2018 - 02:38
Ok.
User
07/06/2018 - 02:38
not zero
Excelchat Expert
07/06/2018 - 02:38
Please select the column, and goto text to columns option.
User
07/06/2018 - 02:38
You saw that I said I already tried that, right?
User
07/06/2018 - 02:39
it changes all the times to AM
Excelchat Expert
07/06/2018 - 02:39
Did you changed the column to date option?
User
07/06/2018 - 02:39
idk what change the column to date option means
Excelchat Expert
07/06/2018 - 02:39
Select the column..
User
07/06/2018 - 02:39
ok
Excelchat Expert
07/06/2018 - 02:40
Please goto Data menu
Excelchat Expert
07/06/2018 - 02:40
Click on Text to columns.
Excelchat Expert
07/06/2018 - 02:40
One pop up comes up.
Excelchat Expert
07/06/2018 - 02:40
2 options will be there.
User
07/06/2018 - 02:41
there is an option to format as date but not time
User
07/06/2018 - 02:41
how will this help
Excelchat Expert
07/06/2018 - 02:41
Please click on Date
Excelchat Expert
07/06/2018 - 02:41
Let's convert that to date format first.
User
07/06/2018 - 02:41
ok done
Excelchat Expert
07/06/2018 - 02:42
It adjusts the text format to date format.
Excelchat Expert
07/06/2018 - 02:42
There will be option as MDY, DMY, etc.
Excelchat Expert
07/06/2018 - 02:42
You can choose what ever you want.
Excelchat Expert
07/06/2018 - 02:42
Ok. Now, the text will be in date format.
Excelchat Expert
07/06/2018 - 02:42
Please try with timevalue function.
User
07/06/2018 - 02:42
just so you know, once we do this, there will be one column with the date, one column with all the times - switched to all AM, and one column with the correct AM/PM for each entry
User
07/06/2018 - 02:43
but i will do as you wish
Excelchat Expert
07/06/2018 - 02:43
Is it possible to share your excel file with one row of data into the chat?
User
07/06/2018 - 02:44
it did exactly what i told you it has been doing the whole time, as the issue is not with the date format but the fact that it extracts the AM/PM as a separate column when you use text to column
User
07/06/2018 - 02:44
yes i did - row 16
Excelchat Expert
07/06/2018 - 02:44
Are you getting error now?
Excelchat Expert
07/06/2018 - 02:44
If not, you will get the time.
Excelchat Expert
07/06/2018 - 02:45
Oh. Text to column should not extract it.
User
07/06/2018 - 02:45
i added another row
Excelchat Expert
07/06/2018 - 02:45
Are you applied delimit of space?
User
07/06/2018 - 02:45
see how it says 3:00 and then PM next to it
User
07/06/2018 - 02:45
the 3:00 has been changed to 3:00 AM
User
07/06/2018 - 02:45
hence the issue
User
07/06/2018 - 02:45
yes
Excelchat Expert
07/06/2018 - 02:45
Oh.
User
07/06/2018 - 02:45
there is a space between the time (3:00) and the period (AM/PM)
Excelchat Expert
07/06/2018 - 02:46
You have to remove the delimit option.
Excelchat Expert
07/06/2018 - 02:46
remove the checkbox.
Excelchat Expert
07/06/2018 - 02:46
Use fixed width option.
User
07/06/2018 - 02:46
ok
Excelchat Expert
07/06/2018 - 02:47
Don't use delimit opiton.
User
07/06/2018 - 02:47
yassss
User
07/06/2018 - 02:47
ty
Excelchat Expert
07/06/2018 - 02:48
It works?
Excelchat Expert
07/06/2018 - 02:48
Great to hear that. When you are using delimit, it split the text. So, you have to use fixed option.
User
07/06/2018 - 02:48
word
User
07/06/2018 - 02:48
i knew it must be something obvious
Excelchat Expert
07/06/2018 - 02:49
Please do visit Got It Pro. Thank you. Have a wonderful day ahead.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.