Column 1 = What I entered in cell.
Column 2 = What formula bar shows when cell is selected.
Column 3 = What cell shows when cell is not selected.
1 | 2 | 3 ----------------------------------- 30-07-17 | 30-07-17 | 30-07-17 10-08-17 | 10/8/2017 | 08-10-2017 13-08-17 | 13-08-17 | 13-08-17 09-09-17 | 9/9/2017 | 09-09-2017
I tried to retype , I tried changing format , I tried copy-paste format : nothing works ; rows 2 and 4 are always like this, even though format is same for all cells. What is wrong ? Is this a bug in Excel ?
I am on windows 10, using Excel 2016.
Based on inputs given by @ScottCraner:
If DATE string in cell is invalid according to local settings (eg when MM = 13 or 30) the string will not be changed, else it will be show DATE in some format (eg "DD-MM-YYYY" format, which may mix MM & DD).
I have to type it in "MM-DD-YYYY" and it will show in "DD-MM-YYYY" format, which is what I want.
One Part is resolved, but Doubts remain:
When DATE is invalid, why Excel shows no warning ?
Rows 2 & 4, why "-" changes into "/" ?
With more Info from @RonRosenfeld , I think Issue is resolved.
(1) If DATE Entry string is invalid (according to regional settings), then cell will Display as it is, with no formatting.
(1A) The formula bar will also show the text as it is.
(1B) In general, there will be no notifications to user. If user wants notifications of this error, he has to use Data Validation.
(2) If DATE Entry string is valid (according to regional settings), then cell will Display in format given by user.
(2A) The formula bar will show it in regional short setting.
(2B) There is no way to get back what exactly user typed.
Excel is automatically changing things , which is a little confusing !
Thanks @ScottCraner & @RonRosenfeld !