I need help converting pasted data to it's correct value so I can sort properly

In the example worksheet, you'll see in columns A, B and C you see what looks like the date, time and numbers but since they were pasted all are text values I believe.

I column A, I need the values to be the actual date value.

I need to convert column B to actual time value so I can have either 12 hours or 24-hour format.

In column C, I have names

In column I have numbers


Once I can convert all the pasted text to the actual date, time formats. Once I get that done I need to be able to enter a name in column "H" and have it query column D and C and enter the total amount for each name.


I only know enough about Excel 2010 to get myself in trouble. I had this formula once before but somehow it got deleted by mistake. IF someone would be kind enough to show me how to do everything I listed above I would appreciate it very much.

thanks for the help but as I said I don't know much about Excel and I know NOTHING about VBA So I will never be able to understand what I am doing using your way.

I appreciate your time you spent trying to help me but I know that VBA is out of my league, I may be able to follow your instructions and get in done, however, I won't understand what I did. Plus you didn't address the formula for the totals section???

Maybe somene will reply with an easier way to do the same thing without VBA.

Once I get the date and time in the proper formats, I need to have a formula that will search column with the names and numbers and give me a result for each name. As I add more names and amounts the formula will always update the section that has the totals. I hope that explains what I need to do with this data

Thanks,alansidman for the formula, I tried it and it works.
Now I just need to make sure the date and time
are actual dates and time and not text since I pasted it.

Shouldn't I be able to right-click on the columns and select the format I want for each column and that will
convert the text ( if it is text )

Originally Posted by bebo021999

Helper column in E3 to produce real date :

Please Login or Register  to view this content.

Copy/paste special back to A3 if needed.
Time in B column I believe it is real time.

Thanks Bob, I'm looking for a simple way that I can understand. I haven't a clue what your example means or what or how it works.

Guys I am trying to do this the simplest way possible so I can remember how to do it in the future, sticking to ways I understand will do that for me

Doing that now

Originally Posted by alansidman

Did you look at the link I provided in post 4. It offers several solutions to converting the dates and explains how and why they work.

When I follow the instructions on the link your provided I get a #VALUE!

Originally Posted by alansidman

I have a solution for you but it is multi step and may not be any easier for you than what Bebo has offered.
1. Highlight Column A and then select Data--Text to Columns on the Ribbon
2. Delimit the range with Space
3. Have the wizard paste the new data in E3. You may have to move your list of names to avoid them being overwritten with this new data.
4. In Column N. Type each month as shown in Column A. ie. Jan, Feb, Mar, etc
5. In column O. type the month number next to each month, ie. 1, 2, 3, etc
6. In H3, type =DATE(G3,VLOOKUP(E3,$N$3:$O$14,2,0),F3)
7. Copy formula in H3 down.
8. Highlight H3 down to the last item and copy
9. Position your cursor in A2 and paste special values.

Lengthy, but it does work and you should be able to understand.

First of all, you said, "I have a solution for you but it is multi-step and may not be any easier for you than what Bebo has offered." That may be true but Bebo really didn't explain how to do his method, I stilll appreiate his effeort to help me.

in your very detailed explaination I do not see "Data--Text to Columns" on any of my ribbions. I see more than I wonder if I will be able to follow it but I need to do it one step at a time for now and looking for the Data--Text to Columns is what I am doing now. if you see this reply please tell me which ribbon and approximately where it is. that will help me a lot.

Originally Posted by alansidman

I have a solution for you but it is multi step and may not be any easier for you than what Bebo has offered.
1. Highlight Column A and then select Data--Text to Columns on the Ribbon
2. Delimit the range with Space
3. Have the wizard paste the new data in E3. You may have to move your list of names to avoid them being overwritten with this new data.
4. In Column N. Type each month as shown in Column A. ie. Jan, Feb, Mar, etc
5. In column O. type the month number next to each month, ie. 1, 2, 3, etc
6. In H3, type =DATE(G3,VLOOKUP(E3,$N$3:$O$14,2,0),F3)
7. Copy formula in H3 down.
8. Highlight H3 down to the last item and copy
9. Position your cursor in A2 and paste special values.

Lengthy, but it does work and you should be able to understand.

OK I got what you meant, ON the "data" tab select text to column. I tried it and it didn't have an option to "Have the wizard paste the new data in E3" So I couldn't get past that part.

My time is formatted as time, the formula you gave me works fine with the little adjustment I made from row 500 to row 5000 it will handle any entries I will ever have to enter.

So now the only problem I have left is the column A Dates is not formatted correctly, I try to sort by column A ( sort by date ) abd it sorts but it sorts Apr all years. It should sort with first month and year and so on now it sorts all April 2012 then May 2012 then June 2012 it should go Jan 2012 through June 2018

Originally Posted by alansidman

Here is a picture of the step that you need to do to get the data into cells E3:G3

Attachment 579197

For some reason it will not allow me to open your image, when I wasn't logging in I
saw the link but once I logged in there was no longer a link. I think I can do it
now with your formula and now that I got all the columns formatted to what they
should be. Now all I have to do is put your formula into the first cell of
the names. I am using this formula you gave me the first time =SUMIFS($D$3:$D$500,$C$3:$C$500,H8) and copy down

I did it a bit different, I didn't have to use this formula "=DATE(G3,VLOOKUP(E3,$N$3:$O$14,2,0),F3)" Not sure what that one was supposed to do but now that I got my dates in the correct format everything works fine. I will attach the emaple to this post and you can try it yourself to see if it will work. all you ahve to do is add a date, time name and amount and then go to column"H" type the name you added and the amoooount shold fill in and that is exactly what I needed this worksheet dto do.