Date format

[rae30]

I have a data dump that I have to format in Excel and I'm having difficulty formatting the date.

For example 01/05/2015 formats to Jan 5th 2015, however it should be May 1st. How do I fix this.

 

[oldbrewer]

the 01/05/2015 is clearly mm/dd/yyyy

so jan 5th 2015 is correct

 

[AlphaFrog]

I have a data dump that I have to format in Excel and I'm having difficulty formatting the date.

For example 01/05/2015 formats to Jan 5th 2015, however it should be May 1st. How do I fix this.

Are you importing the data from a CSV or text file of some sort? If yes, how are you doing it?

 

[Joe4]

It has to do with your Microsoft regional settings.
If you have the default American settings, it reads short dates as m/d/yyyy
If you have the default European settings, it reads short dates as d/m/yyyy

You can change those settings.
See: https://support.office.com/en-za/ar...ta-types-edf41006-f6e2-4360-bc1b-30e9e8a54989

 

[gaz_chops]

ADVERTISEMENT

the 01/05/2015 is clearly mm/dd/yyyy

so jan 5th 2015 is correct

I would disagree, it is clearly dd/mm/yyyy so therefore 1st May is correct

 

[rae30]

I would disagree, it is clearly dd/mm/yyyy so therefore 1st May is correct

Ummm no. When I say May 1st is the date, it's the date I want. I cut and paste the date from another source into excel, and there are over 2000 lines that I don't fell like manually changing.

 

[gaz_chops]

ADVERTISEMENT

Umm yes, see Joe4's answer above.

 

[arthurbr]

Try selecting the range - Data - Text to columns - Next - Next and play with the date format in the last window. Try DMY or MDY, I'm not certain

 

[deletedalien]

It has to do with your Microsoft regional settings.
If you have the default American settings, it reads short dates as m/d/yyyy
If you have the default European settings, it reads short dates as d/m/yyyy

You can change those settings.
See: https://support.office.com/en-za/ar...ta-types-edf41006-f6e2-4360-bc1b-30e9e8a54989

As an excel user in Mexico i have to Clearly concur with Joe4's answer posted above,

i learned excel using its english version and when i have tried to use excel in spanish (the 2 times that i have) i have had nothing BUT trouble with date formats...
so i always return to the english version for this.

Also arthurbr's answer might be the way to go here

you might need to split your date from your datadump using helper cells and Then concatenate to obtain the correct date format, because you can lie to your self but not to excel..... sorry but that's my honest opinion....

 

[Rick Rothstein]

Select the column with your "dates", call up the "Text To Columns" dialog box (Data tab, Data Tools panel)... select Delimited and press Next twice an select the Date option button (top left section of dialog) and then select DMY from the drop down list... click the Finish button.