Force cell date format

[Macdave_19]

Hi folks

A question i'm sure most of you can help with:

I need to force the date in column b to always default to the following format

DD/MM/YYYY

Because some idiots like to put is like this:

DD.MM.YYYY

even if i format the cell to have the format that i desire the user can still enter the date with the fullstops!!

any idea's on how to stop this?

I Appreciate you reading this and cheers for any help folks!!

[Special-K]

Use a helper cell to turn it into TEXT

Perform the following checks using IF/AND conditions

1) Is the LENgth of the value input 10 characters ?
2) Are the 3rd and 6th characters "/" ?
3) Is the VALUE of the first 2 characters from 1 to 31 ?
4) Is the VALUE of the 4th and 5th characters from 1 to 12 ?
5) Is the VALUE of characters 7-10 from 1900 to 2007 (you'll have to change these two values depending on what years are valid) ?

If all these conditions are true then a date has been entered in the format DD/MM/YYYY

You may want to check that the date (DD) entered conforms to the numbers of days in the month (MM) entered, and allowing for leap years in the year (YYYY)

[slaponte]

Maybe an easier way... (pardon if wrong, I am a newbie)

I have the same issue, in that the cell is format for MM/DD/YY but it will allow the entry of MM//DD/YY (notice the extra slash). Then, calculations fail.

I set VALIDATION for the cell to only allow dates between 1/1/1900 and 1/1/2199. To validate the date, excel checks it and gives out an error. I tried and it won't allow the dots either. And is easier than all the ifs and ands...

Hope that helps.