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?