0

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.

UPDATE: 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 "/" ?

ANOTHER UPDATE:
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 !

  • 1
    It thinks you are entering a date, while the others excel sees as strings as it cannot be converted to dates. If you want dates then you need to check you local settings as Excel thinks you local date is mm/dd/yyyy not dd/mm/yyyy. If you want strings then change the whole column to text and reenter the data. – Scott Craner Oct 23 '17 at 18:21
  • @ScottCraner , that sounds right , but I had tried to set the format of the cells to dd/mm/yy ; Problem still persisted. Maybe the cell format is only to display , but I have to type in mm/dd/yy ? Yes , I retried now , it works ! thanks ! – Prem Oct 24 '17 at 6:55
  • @ScottCraner , still I have some Doubt : Why column 1 changes into column 2 , when it could not convert the DATE ? Here , "-" changes into "/" ! – Prem Oct 24 '17 at 6:59
  • 1
    To answer your Doubts: If you want Excel to only accept valid dates and give you a warning, you use Data Validation. The difference between what you see in the formula bar vs the cell when you have entered a valid date: the formula bar will show dates in the format of the Windows Regional short date setting of the computer. The cell will show the date formatted according to the Excel cell's number format. – Ron Rosenfeld Oct 27 '17 at 2:04
  • @RonRosenfeld , thanks for the info! I will include it in question – Prem Oct 27 '17 at 9:19
1

With Info from @ScottCraner & @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 !

0

Use the formula =TEXT(A1, "dd-mm-yyyy") Change it around to meet your specific format. You may want to clear the format of the cells that you are going to use the formula in.

  • welcome to StackOverflow !! thanks for your workaround !! – Prem Oct 24 '17 at 7:01
0

Had an issue like this with a macros. The problem relies on the local var for dates.

You can force a date like this =Day("A2")&"/"&Month("A2")&"/"&Year("A2") This will only work if you need strings.

Also there is this "weird"(for me, at least) format number "m/d/yyyy", this will take you local var and output the correct date. This one worked for me consistently, otherwise days and months will constantly swap around without anylogic. I used this one like this in VBA .range(rng).FormatNumber = "m/d/yyyy"

Cheers

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.