Excel - how to force date as dd/mm/yyyy for data validaiton

Question

may I know how to force date input to dd/mm/yyyy using data validation?

Cell in here would be A1.

I formatted my cell to

Category: Date

Type: 30.11.2017

So if I type 11.30.2017, it will changed to 30.11.2017 which is good. What I wanted it to be.

I also formatted the cell using data validation as I only want date format for the cell.

Allow: Date

Data: Greater or equal to 'Does not matter

Start date: 1.30.1966 'Does not matter

Error Alert is ON

When setting Data validation, I'm unable to input format in dd/mm/yyyy because my system date is in mm.dd.yyyy format.

When excel is used by other users with system date formatted to dd/mm/yyyy, they will input the date as dd/mm/yyyy but that will triggered the error alert because the format is not acceted.

Besides Data Validation and Formatting Cell, I also added date-picker to the cell. But the same thing occurs for date-picker as it follows the system format.

In a nut shell, how can I force the cell to be in date format specifically dd/mm/yyyy even when their system format is set to mm.dd.yyyy or any other format?

Update

Sorry if I did not make it clear. This is my data validation setting. As you can see, I purposely set my region short date format to mm.dd.yyyy the data validation for start date and end date would also be in the same format. How can I force the data validation date format to dd/mm/yyyy?

Solution 1

In international environments, keep things simple. Just stick with the defaults and don't try to re-invent the wheel.

Use one of the first two date formats from the list. They have an asterisk, which is an indicator that this format will be adjusted to align with the locale to show correct in the language and region that is used when the workbook is opened.

Then set data validation to accept only dates and set the range between 1/1/1900 and 31/12/3000. That should cover your data validation in any region and locale.

Then leave it up to Excel and Windows to sort out the local date format and the way to enter a date correctly.

Edit: to remove all possible problems with locale, set the data validation date range to dates where day and month are interchangeable, like from 1/1/2000 to 1/1/2099. That should take care of all locales.

Solution 2

If you want to be able to support multiple user locales, but enforce a certain date format, you'll need to format your cell as Text, and apply data validation to that cell. The Text formatting will prevent Excel from auto-converting the entries to a date, but if you need to use the text value in subsequent calculations, then you'll need to firstly add another cell of name, that converts the text to a date, and then use that cell in your subsequent calculations.

For example, in a cell A1 I enter 30/11/2017. Then, in cell A2, I enter the formula: =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)) and, then cell A2 is an Excel formatted date, in the locale/regional settings of the user, and can be used in subsequent calculations.

Comments

[Max]

Thanks for answering, so for your method, I will have to change my system date format back to dd/mm/yyyy in order for me to set the date at data validation right? Currently my system date format is mm.dd.yyyy

[teylyn]

No, you don't need to change your system date format. Excel will honor your system settings and display the date according to your system's regional and language setting. Use only the date formats with an asterisk and Excel will adjust the date display to the regional settings. If you hard code a date format and not use an asterisk format, THAT is where the trouble starts.

[Max]

Yes, I agree and understand that. My problem is when users's regional setting for date is mm/dd/yyyy, the data validation would auto change to 12/31/3000. I want to force the data validation as 31/12/3000.

[teylyn]

Why? Why do you want to force a format that is not a date for the user? Regardless of the user's date format, if validation is set to accept only dates, then everything will be fine and the result will be a date. The key is to have data validation to allow only dates. Don't muck around with any other validation type. What are you trying to achieve by forcing the user to enter a date in a format that is not a real date on their computer?

[Max]

Alright. Thanks for the explanation. meaning the date format for data validation would follow the user's regional setting no matter what, which means I'm unable to force the format to dd/mm/yyyy. Is that correct?

[teylyn]

Can you explain why you would want to force the user to enter date in a format that is not a date for them? What is the reasoning behind that? If you want the result of the data entry to be a real date, then just use data validation with date and let Excel do the rest. Apparently you don't want real dates and means of entry is more important than cell result??

[teylyn]

How are you defining your data validation?

[teylyn]

Let us continue this discussion in chat.