Problems with the date

[Xenoun]

I know people often have problems with the date settings, i've tried looking around to find possible solutions and have tried a few things.

My regional settings were on United states, they are now on australia.
The date columns in my spreadsheet are set to australian format.


What appears to have happened is that some of my date entries excel sees as a date, whereas the rest it sees as text, even though it's in the appropriate date format. These date values have come from another sheet which may or may not have the correct settings...its harder to tell.

What appears to happen is that when a date with non-australian format is entered excel sees it as text and ignores it. When the correct format is entered excel treats it as a date. This seems weird, as quite often if the sheet is set to united states format and i type a date in the australian format then excel will automatically change the date to the american format (which is quite annoying). Yet here I have the reverse, some dates are in united states format but instead of changing it to the australian format excel just treats it like text.


Is there any way to solve this other than re-typing every incorrect date? That isnt really possible for me as i'm dealing with a lot of data

 

[GlennUK]

You say that the dates come from another sheet ... are the dates in a single column? If so, select the column, do Data/Text To Columns, and process as a date M/D/Y data column ... it should correct it to your settings. ( I know it's not intuitive, but try it before saying it's not what you want to do ... and make sure to use the date setting I've recommended, and not the one you think it should be ).

 

[Xenoun]

Posted a reply before realising this was a different thread...my problem was solved elsewhere =)