Help with changing date format

[monikatdlt]

Hey y'all,

I recently made some sheets of data on Doctor Who episodes (yes, nerd alert!), and I got the data from Wikipedia, so the air dates are in a European format, (23 November 1986, etc). I've been trying to change them to numbers and to make them more consistent and easier to work with, (and because I also want to import these to Kaggle as well) but I haven't been able to figure it out. If I change the date format, nothing changes. I used the LEFT and RIGHT functions to pull out the years and dates, but then the month names are different lengths, and also the single-digit dates are only one digit, not two, which kind of messes things up.

I tried using the Text to Columns wizard, which mostly worked, but not on all of the entries, for some reason.

Hopefully y'all can help me figure this out, thanks in advance!

 

[rlv01]

What about instead of text to columns, you use the Datevalue function?

Book2
	A	B
1	4 April 1964	04/04/64
2	20 June 1964	06/20/64
3	18 July 1964	07/18/64
4	12 September 1964	09/12/64
Sheet3

Cell Formulas
Range	Formula
B1:B4	B1	=DATEVALUE(A1)

 

[monikatdlt]

What about instead of text to columns, you use the Datevalue function?

Book2
	A	B
1	4 April 1964	04/04/64
2	20 June 1964	06/20/64
3	18 July 1964	07/18/64
4	12 September 1964	09/12/64
Sheet3

Cell Formulas
Range	Formula
B1:B4	B1	=DATEVALUE(A1)

I did try that, but I just get a VALUE error

 

[wolf735]

an idea is check the space to see if it matches the default space.

 

[rlv01]

Assuming the dates are in col I as shown above, try changing the cell format of those cells to 'Text' then try the Datevalue function again.

 

[monikatdlt]

So, I'm not sure how, but I accidentally fixed it lol. I copied each sheet into Google Sheets, and the DATEVALUE function worked perfectly. Other formulas like VLOOKUP that I hadn't been able to get to work also worked, so I just copied everything back into Excel and voila! Maybe there was some odd formatting left from copying from Wikipedia or something that I wasn't aware of? At least now I know that I'm not completely incompetent, so that's a plus lol