How to change default date format to dd/mm/yyyy "while editing" a cell


As part of maintaining accounts of my clients, I have to enter a lot of dates in excel. The "Format Cells" setting of MS Excel only changes the way a date is displayed, but it doesn't change the way a date is entered. In India, date format followed is dd/mm/yyyy. Whenever I need to manually enter a date in a cell, I have to enter it in mm/dd/yyyy format. Even if I change the format to dd/mm/yyyy, it will just show the date in this format. When I edit a cell with a date, it does not take the input in dd/mm/yyyy.

Let me take an example - If I need to enter 4th July, 2016, I have to enter 07/04/2016. If I enter 04/07/2016, the cell will start showing the date as 7th April, 2016. If I change the format of the cell to show the date as dd/mm/yyyy, it will show the date as 04/07/2016 but as soon as I double click the cell, it will change the date format to mm/dd/yyyy and expect me to enter date in this format only. This is a huge confusion for my other accountants who are not techies and not aware of US date system.

Is there a way or setting in which I can change the default date format of my excel to dd/mm/yyyy even for entering a new date or editing an existing date?


Rishabh Agrawal


Re:  date format in excel

The default date format is a Windows setting not an Excel setting.
Take a look at whatever MS calls Control Panel in Windows 8 and adjust your "Regional & Language Options."

Jim Cone
Portland, Oregon USA (Dropbox)
(free & commercial excel add-ins & workbooks)

65 people were helped by this reply


Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.


Question Info

Last updated August 11, 2020 Views 20,192 Applies to: