Cant change date format, seems to be half text?

[wcngu1]

Hi guys!


I'm embarrassed to have to ask this but when I exported some data to a CSV for some reason half the dates in the column is General format and not in Date format.
I tried changing the format to date but it makes no difference at all.


It is driving me insane =/


Any help appreciated! The file is here: https://drive.google.com/open?id=1Ss3l_aMWb4m-MM5ZpKBD6_RPE-_9hdfC

The date format is correct from A2-A261. But from A262 it changes format out of nowhere and wont budge. Any pointers would be great!


Thanks in advance guys. Its probably the most amateur question ever.....

---

 

[mikerickson]

I can't tell for sure how your Excel sheet is formatted by looking at a google doc, but it looks like A262 downward is text.
Put
=datevalue(A262)+ timevalue(trim(mid(A262, find(" ",A262), 255)))
in B262. Use the format painter to put A261's format on B262
Drag down
Then copy / paste special values

 

[wcngu1]

I can't tell for sure how your Excel sheet is formatted by looking at a google doc,

Thanks for your reply mike! For the link you can actually download the excel sheet with the download button on the top right.

I tried to insert your formula but it says #Value1 , I must be doing something wrong..... I'll keep trying!

It does say that the wrong dates are in the General format... =/

 

[mikerickson]

It worked for me.

 

[wcngu1]

It worked for me.

It seems to be a problem in my excel..... we just changed over to 2016 version.

I will try this on my own computer tonight! Thanks for the solution Mike if it works for you then its time for me to sort my excel out arrrgh

 

[morningstar1001]

I think your problem is the format.
It mixing between format dd-mm-yyyy, and mm-dd-yyyy. Depend on your local setting, excel will try to convert from CSV to 1 of these 2 format.

So 12 jul will be convert to 7 dec which result format is date, and 13 jul can't be convert and keep the same as general.

You should change the display format to dd-mmm-yyyy to check. Then if that correct try to parse the month, day value back to correct order.

 

[wcngu1]

Tried a different computer with an older excel and it worked! thankyou all for your help!