Solved Data formatted as a number; not a date

August 17, 2011 at 08:33:28
Specs: Windows Vista
I have a spreadsheet that I pulled over (CTRL C) from a webpage and inputted (CTRL V) into an excel document. The excel file has over 1,000 lines in it and I need to organize this data chronologically. The issue that I have is that the dates are listed as m/d/yyyy, but these dates are not formatted as dates for some reason. I need to figure out how change the dates to the yyyymmdd format, so I can organize all data chronologically.

I have already tried doing the FORMAT CELL/NUMBER/DATE, but it is not allowing me to change the date, so I am assuming that the numbers are formatted as a text or number and are not formatted as a date.

Can anyone here help me?


See More: Data formatted as a number; not a date

Report •


✔ Best Answer
August 19, 2011 at 01:33:32
Try:

=DATEVALUE(LEFT(C2,LEN(C2)-1))

Select the column...right-click...Copy...right click...PasteSpecial...Values

Format...Cells...Custom...yyyymmdd

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
August 17, 2011 at 09:06:05
Assuming your dates are in Column A, pick an empty column, enter this formula and drag it down:

=DATEVALUE(A1)

You should get a series of 5 digit numbers since that is how Excel stores Dates internally.

Next, format that column using:

Format...Cells...Custom...yyyymmdd

Then select the column and do right-click...Copy...right click...PasteSpecial...Values

You should now have a column of Dates formatted in the manner that you want.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 17, 2011 at 11:47:04
Wow! Thank you for the quick response!!

I understand everything else, but where do I enter the formula?

Thanks again!


Report •

#3
August 17, 2011 at 12:08:36
Didn't I say "Pick an empty column"?

It doesn't matter what column you use as long as it refers to the cells where the text-dates are.

For example, it the troublesome "dates" are in A1:A1000 , insert a new Column B and enter
=DATEVALUE(A1) in B1 and drag it down to B1000.

Then follow the rest of the steps in my earlier response. Once you have the working dates in Column B (after the PasteSpecial...Values) you can delete Column A since it's not doing anything any more

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
August 17, 2011 at 12:53:10
I think I am doing something wrong.....Here is what I have done, step by step:

1. I broke down my spreadsheet and I have all of the dates already popluted in Column C with the first one in cell C2 as "1/12/2011" all the way down to cell C436 as "9/30/2010".

2. I opened inserted a blank column to the right in column D. I started with the first cell directly to the right, cell D2 and entered "=DATEVALUE(C2)" and dragged it all the way down to cell D436; i.e. making cell D235 read "=DATEVALUE(C235)"

3. As soon as I hit enter, the cells all change to read "#VALUE!"

4. Not knowing if this is because there was no value in the cell or not, I pressed forward and formatted the column and then did the paste special.

I think I am doing something wrong in step 2, but not quite sure what it is.

Thanks for your patience and your guidance; I am not an excel expert by no means.


Report •

#5
August 17, 2011 at 13:17:16
Do the "dates" in Column C actually have quotes around them?

Since quotes mean something specific in Excel, you shouldn't use them when you post data or formulas unless they really exist. That only adds to the confusion.

I will assume that they do not have quotes around them.

It's very possible that when you pasted the data from the webpage it brought along some extra characters, perhaps a space or a hidden/unprintable character.

That could cause the DATEVALUE function not to recognize the contents as a "text date" and thus return #VALUE.

Try one of these in D2 to see if any of them return a 5 digit number. Try them in the order listed.

=DATEVALUE(TRIM(C2))
=DATEVALUE(CLEAN(C2))
=DATEVALUE(CLEAN(TRIM(C2)))
=DATEVALUE(TRIM(CLEAN(C2)))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
August 17, 2011 at 13:29:06
Your assumption is correct, the dates do not have the quotes around them on the spread sheet.

I have tried the formulas in the order above and I am still getting the same message. I can remove the names, ssn's, and other personally identifiable information on this document and send it to you, so you can see what I am using if that would help. Or can I post it in here somehow?


Report •

#7
August 17, 2011 at 14:17:56
derbydad03.....

Okay, after reading and reading your previous posts, I have figured out the issue. The dates when pulled over actually placed a space after the date in cell C2 of 1/12/2011 with a space after the 2011 .

Once I deleted the space, I was able to change the cell to a date and further to the correct format using FORMAT CELLS/NUMBER/CUSTOM/yyyymmdd

Is there a way to remove the spaces at the end of the dates? I believ that this could easily solve the problem.....I think.


Report •

#8
August 17, 2011 at 15:24:03
The TRIM function is used to remove "excess spaces. From the Excel help files:

TRIM: Removes all spaces from text except for single spaces between words. 
Use TRIM on text that you have received from another application that may 
have irregular spacing.

You can not post files in this forum.

If TRIM doesn't work, you can send the file to the email address I've sent to you via Private Message. Please do not share this email address with anyone.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
August 18, 2011 at 07:30:45
I have sent the file to you via email.

Please let me know if you have any questions about his file. Thank you very much for your patience and your help!


Report •

#10
August 19, 2011 at 01:33:32
✔ Best Answer
Try:

=DATEVALUE(LEFT(C2,LEN(C2)-1))

Select the column...right-click...Copy...right click...PasteSpecial...Values

Format...Cells...Custom...yyyymmdd

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
August 19, 2011 at 07:06:28
You've got it!!

Works perfectly!

Thank You VERY MUCH!!

First time user of this forum, awesome place!


Report •

Ask Question