Number being converted into a date by Excel

missmary4

New Member
Joined
Dec 26, 2006
Messages
22
Hello,

I have a product number 11-3560 that, when exported into a .csv file from a website, gets automatically converted into a date. When I change the format back to Number, it changes the value to 606609.00 (the numerical date equivalent of the incorrectly used date).

Does anyone know how I can restore the cell to its original numeric value? Unfortunately I can't just use an apostrophe to ignore Excel's auto format since it is exported from an external website.

You will notice too, if you type this number directly into a blank cell, it will do the same thing (if it is in General format).

Thank you!
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

marious

Board Regular
Joined
Mar 3, 2013
Messages
219
There is an option in the driver when you import TXT, CSV, xlsx, xls, etc that will let you select the particular column and select the import as TEXT. This way excel doesn't guess for you Excel 2013 64 bit import driver: 1. Delimited, Fixed, My data has headers. 2. Tab, Semicolon, comma, space, other, 3. General, Text, Date, Do not import this column and Advanced " Remember to select the pertinent column and then select the desired format 3. Finish
 

missmary4

New Member
Joined
Dec 26, 2006
Messages
22
There is an option in the driver when you import TXT, CSV, xlsx, xls, etc that will let you select the particular column and select the import as TEXT. This way excel doesn't guess for you Excel 2013 64 bit import driver: 1. Delimited, Fixed, My data has headers. 2. Tab, Semicolon, comma, space, other, 3. General, Text, Date, Do not import this column and Advanced " Remember to select the pertinent column and then select the desired format 3. Finish

This sounds perfect; I used the text import wizard on the data tab and performed the operations you mentioned. Strange though, that even when I changed the column to TEXT format, the number still says Nov-60 for this part. But the cell states it is TEXT. There must be some other issue I am overlooking.

Thank you again.
 
Last edited:

marious

Board Regular
Joined
Mar 3, 2013
Messages
219
This sounds perfect; I used the text import wizard on the data tab and performed the operations you mentioned. Strange though, that even when I changed the column to TEXT format, the number still says Nov-60 for this part. But the cell states it is TEXT. There must be some other issue I am overlooking.

Thank you again.

Please read this article Excel JULIAN DATE?? IF the answer is not there I will give it a try on Monday. You have a number that is probably a Julian number. here in this post you will find diferent ways to convert to date. It might be also just the 1900 series date format that excel uses. I Hope is helps. I have a question. Are you importing a csv into excel or exporting from the internet a file and it creates automatically a csv file. Regards, Mario
 

Watch MrExcel Video

Forum statistics

Threads
1,108,484
Messages
5,523,212
Members
409,504
Latest member
Joshcurrie

This Week's Hot Topics

Top