When working with date formats in Google Sheets, one common issue users face is discrepancies in how dates appear in dropdown menus versus how they are displayed in the cells.

Particularly, the problem arises when dates formatted as dd/mm/yyyy in cells revert to mm/dd/yyyy when selected from dropdowns.

This can cause confusion and lead to data entry errors. In this blog, we will explore a straightforward solution to ensure that date formats remain consistent in both the cells and dropdown lists, enhancing the accuracy of your data management.

Step 1: Format the Source Data

Firstly, it’s essential to format the source data from which the dropdown is created. This ensures that all dates are in the desired format (dd/mm/yyyy in this case) before they are even added to the dropdown menu.

Select the cells or column containing the dates you want to use for the dropdown.
Navigate to `Format` → `Number` → `More Formats` → `More date and time formats`.
Customize the date format to `dd/mm/yyyy`.
Click on “Apply” to save the changes.

Step 2: Create the Dropdown

After you’ve formatted your source data:

Choose the cell where you want to implement the dropdown.

Go to `Data` → `Data validation`.
 Set the criteria to “List from a range” and select your formatted date range.
Make sure “Show dropdown list in cell” is checked.
Click “Save” to apply the dropdown with the correctly formatted dates.

Step 3: Check Locale Settings

If the dropdown still displays dates in the mm/dd/yyyy format, despite correct formatting, the issue might be related to Google Sheets’ handling of locale settings:

Open `File` → `Settings`.
Under the “General” tab, look for “Locale”.
Change the locale to a region that uses the dd/mm/yyyy format by default, such as United Kingdom, Australia, or in this particular case, English (India).
Save your settings, which should resolve the issue and ensure the dropdown reflects the dd/mm/yyyy format.

Conclusion

By ensuring that the source data is formatted correctly, creating the dropdown properly, and adjusting the locale settings, you can maintain consistency in how dates are displayed in Google Sheets.

This approach not only improves the visual consistency but also prevents potential confusion and errors that might arise from format discrepancies. Google Sheets’ flexibility with locales and formats allows users worldwide to tailor their experience to meet local standards and personal preferences, making it an invaluable tool for global data management.