Dear Gary, I have a column with dates, but the original source excluded the seperation. Like 20200205, which in this case means 5th of February in 2020. I try to change the format to date, but Numbers refuses to change it. Ho can I change the column format to show 2020.February 05, instead of 20200205?
Thank you
—–
Orgati
That's called a Julian date. I'd imagine that Numbers isn't even recognizing it as a date, but just as the number 20200205. But you can treat it as text and use MID to get the characters from it is three parts. Then put those parts into DATE to get a date.
So if this is in column B, then next to B add a column C. If B2 is 20200205 then put this formula into C2:
=DATE(MID(B2,1,4),MID(B2,5,2),MID(B2,7,2))
All that does is get the first 4 characters, then characters 5 and 6, then characters 7 and 8. So you have "2020" and "02" and "05." Put each of these pieces of text into DATE and you get DATE("2020","02","05) which gives you a real date/time value in the cells of column C.
Once you are done, you can leave it like that. Or, copy all of column C. Then paste it over itself by using Edit, Paste Values. Then you can get rid of the original column B.