How to convert string into a date format?

[jirakst]

Hello,
I need to convert text like 1/13/09 6:13 into a date format, so I can futher manipulate with it. But no matter which format I assign to it, it does not change it's formatting. I've applied date format to the entire left (first) column.

 

[Joe4]

Change the format itself will NOT change the entry of the cell.
To do that, simply highlight the column, go to the Data menu and select Text to Columns.
Hit Next twice until you get to Step 3, and select the Data radio button, and choose the MDY option in that box.
Then click Finish.
All your entries will now be converted to dates, and then you can apply any date format that you want.

 

[jirakst]

I did it, but the issue still continues. I believe it's so, because the date and month are turned over (it change nothing when selected MDY format).

 

[Joe4]

because the date and month are turned over

Not sure what you mean by "turned over".
Care to explain?

 

[RLCornish]

Most of the dates are European date format, but then there are the few thrown in, like 1/13/09 6:13, that are like our US formatting. I think maybe the turned over is referring to the D & M being reverse of normal European format. Would that mix of formats create an issue in and of itself?

Should read 13/1/09. Is this correct, jirakst?

 

[Joe4]

Most of the dates are European date format, but then there are the few thrown in, like 1/13/09 6:13, that are like our US formatting.

I am guessing you can see the image from the first post (unfortunately, I cannot).

The issue is when you have fixed formats, how can you tell which is the one to use?
For example, with dates like 11/6/09, how can you tell if that should mean June 11th or November 6th?
Without having some sort of consistency, it seems that it is anybody's guess!

 

[jirakst]

Most of the dates are European date format, but then there are the few thrown in, like 1/13/09 6:13, that are like our US formatting. I think maybe the turned over is referring to the D & M being reverse of normal European format. Would that mix of formats create an issue in and of itself?

Should read 13/1/09. Is this correct, jirakst?

Exactly! Thank you.

 

[Joe4]

Exactly! Thank you.

See my previous post.
Do you have mixed formats (some US, some European)?
If so, how can tell which are which?

 

[jirakst]

I am guessing you can see the image from the first post (unfortunately, I cannot).

The issue is when you have fixed formats, how can you tell which is the one to use?
For example, with dates like 11/6/09, how can you tell if that should mean June 11th or November 6th?
Without having some sort of consistency, it seems that it is anybody's guess!

I'd like to practice data analysis on sample data, so what I really mind is the format of the dates. But if you know where can be found real, consistent data, it would be greatly appreciated.

 

[Rick Rothstein]

I'd like to practice data analysis on sample data, so what I really mind is the format of the dates. But if you know where can be found real, consistent data, it would be greatly appreciated.

Maybe you can produce your own date data. This formula will produce random dates between (as written) Jan 1, 2007 and Dec 31, 2016 (obviously, change the date limits as needed)...

=RANDBETWEEN(DATE(2007,1,1),DATE(2016,12,31))

The formula will return the date serial number... format the cell as a Date, then copy it down for as many date records as you want for your analysis. If you refresh the worksheet, all of the dates will change, so once you have a mix of dates you like, select the column and then use Paste Special to paste down values only... that will remove the formulas from the cells and lock down the dates as displayed.