Forum Discussion
Forcing a column to only accept one date format
Martin_Weiss Thank you for your answer. I already incorporated the data validation into the worksheet and it works.
My follow-on question is - It seems that some of the historical dates may have been entered in as text. Is there a way to convert all of the data in the entire column to be the same format as a date excel recognizes? Right now, I am manually changing individual cells to =DATEVALUE("xx/xx/xx") but this is not realistic for a big data worksheet. Right clicking and changing just the format to date is not doing anything at all.
Dates are actually numbers and texts are texts, even if they are looks like dates. Applying any format won't convert text to number.
If you have texts with dates before 01 Jan 1900 - there is practically no way.
In other case depends on how you data is structured. If in one column, you may select it, Data -> Text to Columns and on third step of the wizard select Date for the column with source format (e.g. MDY). Texts will be converted to dates and you may apply any desired format for them. Dates in that column won't be affected.