Forcing a column to only accept one date format

[Amotoole1]

We have a worksheet where multiple users are entering in dates so that the number of days in between two dates can be calculated (using Datedif function). My question is, is there a way to make it so the cells only accept one date format for entry. And if so… which is the best way to input a date in which excel will understand for doing datedif calculations?

[#2]

Hi @Amotoole1 ,

for the Datedif function it does not matter, in which format the date was entered. It's just important that it's a date at all.

So I would recommend the following:

1. Format the entry cells in the date format, that you prefer (just for optical reasons)

2. Make a data validation that allows just valid dates:

Here you can choose the option that fits your needs, but in any case, only valid dates will be accepted.

The only way a user could ignore this is to copy/paste from another source. This way, the data validation gets ignored unfortunately.

[#3]

@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. 

[#4]

@Amotoole1 

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.