BleepingComputer.com: Excel - Date Formatting Troubles

I have an application that outputs comma delimited files (CSV) which I have been putting into Excel. However the date format that it outputted by the application is mm/dd/yyyy and does not include leading zeros:

5/25/2010,,"ITEM 1","DESCRIPTION 1",-38.15,,
5/25/2010,,"ITEM 2","DESCRIPTION 2",-36.52,,
5/26/2010,,"ITEM 3","DESCRIPTION 3",2141.04,,
5/26/2010,,"ITEM 4","DESCRIPTION 4",-264.02,,
5/27/2010,,"ITEM 5","DESCRIPTION 5",-77.52,,
5/27/2010,,"ITEM 6","DESCRIPTION 6",-774.76,,
5/27/2010,,"ITEM 7","DESCRIPTION 7",-90.00,,
5/28/2010,,"ITEM 8","DESCRIPTION 8",-4.34,,
5/28/2010,,"ITEM 9","DESCRIPTION 9",-75.83,,
5/28/2010,,"ITEM 10","DESCRIPTION 10",-77.14,,
5/31/2010,,"ITEM 11","DESCRIPTION 11",-7.91,,
5/31/2010,,"ITEM 12","DESCRIPTION 12",-3.71,,
5/31/2010,,"ITEM 13","DESCRIPTION 13",-18.25,,
5/31/2010,,"ITEM 14","DESCRIPTION 14",-26.50,,
5/31/2010,,"ITEM 15","DESCRIPTION 15",-44.00,,
6/1/2010,,"ITEM 16","DESCRIPTION 16",-86.43,,
6/1/2010,,"ITEM 17","DESCRIPTION 17",-7.95,,
6/2/2010,,"ITEM 18","DESCRIPTION 18",-6.43,,
6/2/2010,,"ITEM 19","DESCRIPTION 19",-25.75,,
5/27/2010,,"ITEM 20","DESCRIPTION 20",90.00,,
6/1/2010,,"ITEM 21","DESCRIPTION 21",0.01,,

And Microsoft Excel recognizes some of these as dates - Items 1 to 15 and 20 and formats them as mm/dd/yyyy and are left justified. However Items 16-19 and 21 it also recognizes as dates but puts them as dd/mm/yyyy format and right justifies them.

As I do not have control over the application that outputs the CSV files I have been working on formulas that will put both sets of values into the dd/mm/yyyy format (with leading zeros) format using the formula (I know this could be neater using mid):
=CONCATENATE(LEFT(RIGHT(A1,7),2),"/",IF(RIGHT(LEFT(A1,2),1)="/",CONCATENATE("0",LEFT(A1,1)),A1),"/",RIGHT(A1,4))
And it works fine for Items 1-15 and 20. But when I get to the other dates things start acting weird and I get values like 40/40184/0184 and 40/40215/0215. I removed the formatting from the cells to discover that items 1-15 and 20 held their mm/dd/yyyy format. However items 16-19 and 20 had become numbers: 40184, 40215 respectively.

I am looking for either a reason why this is happening and how to convert these numbers back to the mm/dd/yyyy format or some means to make the CSV file correctly import. I think this has something to do with julian time but any formula I found on the internet converted dates to be wrong. 10/1/2009 became 39823 and the formula
=DATE(INT(A1/1000),1,MOD(A1,1000))
was converted into 02/04/1941.

I am honestly stumped at this point.

There is nothing about the CSV that is causing your issue, as it pastes in fine for me. Based on your description, Excel believes your contents are numbers instead of dates.

If you past the contents into Excel, does it automatically go into separate columns? Are you pasting the contents in Excel, then using Text to Columns or are you importing? When doing the conversion are you telling Excel you want the column to be a date in MDY format?

Try copying your code, then use Paste Special > Text in a new tab in Excel. Highlight column A1, Then use Data > Text to Columns. Choose Delimited, next; Check "Comma", next; click on column that has the dates in it, then click on the Date MYD button, then click Finish.

Do you still have a problem with 16-19 & 21 showing as numbers?

jase30004, on Jun 3 2010, 11:48 AM, said:

The file is saved to my computer from a web server as a CSV file. I have my browser set to auto open CSV files with Excel. I then copy and paste this information from the CSV file to my main sheet that is saved as an excel 2003 file.

jase30004, on Jun 3 2010, 11:48 AM, said:

I am using Excel 2003 and there is no paste special for text - All, formula, values, format, comments, validation, all except boarders, column widths, formula and number values, values and number formats are the paste options. I used values and it pastes as a number. I follow the rest of the steps and it still shows those items as right justified as dd/mm/yyyy format. If I remove the formatting it is back to a number. If I use my formula it still gives me the bizarre output based on that number.

Also, if I put all the data directly into a single cell and do the "text to columns", it still has the same outcome.

This post has been edited by LordSoren: 04 June 2010 - 09:54 PM

I have also tried this on my work laptop running Excel 2007 and still the same outcome. Perhaps this has something to do with the fact that its Canadian localization?

I just copy/pasted your data into EXCEL2007 and then did a "Text to Columns". All the dates were recognised as mm/dd/yyyy format and right justified. And I could convert them to dd/mm/yyyy format by changing the type in "Format Cells".

I tried doing it again using Text to Columns and while it still gives me weird data when I try and use MID, LEFT, and RIGHT functions, being able to select and sort by date is now possible since everything is using the same date format.

Final Solution:

1) Open CSV in notepad
2) Copy data into blank Excel sheet
3) Text to columns and set the MDY format
4) Copy columns to existing worksheet, values only

Thanks for your help, Budapest and jase30004.

Soren

This post has been edited by LordSoren: 23 June 2010 - 10:37 PM