Excel - COLUMN Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc