I have imported a column with many dates, but Excel will NOT read them as dates for some reason. I have looked around and tried doing "Text to Columns" and using "DMY" format. I have also tried simply changing the format of the cells to Date (also Custom 'dd/mm/yyyy'), but nothing works.
Here's what the dates look like (Column A):
09/17/2013 09/05/2013 11/11/2013 11/11/2013 12/29/2013 11/15/2013 12/04/2013 12/04/2013 12/04/2013 12/12/2013 12/04/2013
And I am trying to determine the month(), day(), and year() but nothing works. If I do =Month(a1) it returns January for all of the dates, regardless. However, =Year(a1) returns the correct year...why is month not working?
I have even tried a helper column to split up the dates into simple text, using a formula like =left(A1,search("/",A1)) but that returns a #VALUE error!
What is preventing Excel from seeing these as dates, so I can treat them as such? Thanks for any help or advice!!
Edit: When changing the date to the "Number" format, Excel 'correctly' changes the date format to the numerical 411928 type, so I know at SOME level it knows it's a date. I have then done Text to Columns, using "/" as delimiter, and split the data into three columns. In a fourth column, I combined the splits via =Date(A3,A1,A2) [for 'date(year,month,day)'] but then when I paste that back into the spreadsheet, still can't use =month()...