Change Date Format

[kalpar]

Dear Forum,

I get data in excel sheet from different branches of our organization.my role is to consolidate all the sheets into one and prepare reports.One of the column is a date column and as everybody knows the date format would be different.

It is taking time for me to bring all the data into one format. for instance, I m preparing report for the month of may'15 the date i get from worksheets is just like
9/5/2015
13/5/2015
Actually,these dates should be entered or corrected as 09-05-2015 & 13-05-2015.( I tried changing the column from FORMAT CELLS > CUSTOM>> dd-mm-yyy format.....Some of them change to the desired format & some do not.....I dont understand the reason for it. i even checked, for TEXT format also.....

Every day i m forced to edit the dates manually which is taking time...
I had attached the Screen Shot for easy understanding.... The dates in the filtered list in MONTHS are related to May Month Only....

Pls help me in changing the dates easily



Thanks..... In Advance...

 

[NARAYANK991]

Hi ,

I think your system date format is mm/dd/yyyy , which is why something like 16/05/2015 is not being recognized as a valid date.

Can you upload a file with the raw data in it ? If the raw data is in the form of a .csv file , please upload the .csv file.

Narayan

 

[kalpar]

thanks for the reply.. i had uploaded the sample file for your reference with date column only...

 

[NARAYANK991]

Hi ,

The column has a Date format ; I changed the format to General. Can you confirm whether the data in the column now is the original data ?

Can you upload the raw data , which has not been processed in any way ? If you are importing the data into Excel from an external file , please upload that external file , if possible. Otherwise , import the data into Excel with the column format as Text , and then upload the resulting Excel file.

Narayan