• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date Error in CSV

Rinki

Member
Hi,

I am facing weird date error.
When I see the dates in excel, it just looks like they are left aligned or right aligned in the cell. But When I put a filter , I find few of them clubbed into yearwise and rest are as it is in lined up datewise.
Because of this my whole report gets messes up. All the date formats are in general if I check individually.
Is there a possibility that when I save the file from csv to excel 2003 format...it throws an error. I have attached screenshot of the same as well for your reference.
Please let me know what is it that could be going wrong in it.
 

Attachments

  • Date Error in CSV format.png
    Date Error in CSV format.png
    67.2 KB · Views: 10
@Rinki
General isn't date format.
Did You something 'positive' then You opened that .CSV-file?
Have You tried to format those 'looks like dates' to date-format?
 
I just clicked and opened file.... wats positive... formattin all of them into a mmddyyy formats messes my report.
 
Hi Rinki ,

What is your system date format ?

If your system date format is dd/mm/yyyy (and its variants such as dd-mm-yyyy , dd.mm.yyyy) , then irrespective of the cell formats in Excel , the software will only recognize as dates those values which follow the system date format of dd/mm/yyyy.

Thus a value such as 02/11/2016 is deemed a valid date , while a value such as 02/25/2016 is not recognized as a valid date , and is therefore taken as text.

When you import this CSV file into Excel , you need to use the format MDY , so that both the values will be recognized as valid dates.

Narayan
 
@Rinki
You're using 'Filter' in photo.
You can scroll down to see more dates.
or
Close that 'Filter' and go to worksheet to see dates.
 
Hi,


The issue is I am having 2 files ( date 1 and date 2 ) coming from a cloud source in CSV auto forwarded at night in email.

I am supposed to run the report from them saving them in excel 2003 format.

When I run the report from date1….. I get correct output. But when file with date 2 which looks exactly similar date format give me error in my results.


I am confused as to what is wrong in the date format bcz although they look same…. Why I am getting error. Please see the exact file attached and screenshot for your reference… and please help me understand what is going wrong in it.


Also I am importing this files in MS Access to finally refresh my pivots….. so I am wondering is Access making mistake or excel files are fishy..
 

Attachments

  • date file 1.xls
    41 KB · Views: 1
  • date file 2.xls
    207 KB · Views: 1
  • Date Error in CSV format.png
    Date Error in CSV format.png
    67.2 KB · Views: 1
@Rinki ... as I tried to tell with #2 Reply
Select Column A
Press Ctrl+1
Select Date-format

... and You could do that 'format' then You open that .csv first time.
 
Back
Top