A valid date gets changed to custom format in excel

0
votes

I have a file in the format .XLSM ( with macro) i use for keeping orders organised. One piece of information that is entered is a due date.

Locale settings are for belgium (dutch).

a date of 3/1/2020 in the format of d/m/yyyy gets converting to 1/3/2020? a date of 19/2/2020 in the format of dd/m/yyyy gets converted to 2/19/2020. (At this point no code runs, as the only code there is upon closing the file.)

When i check cell properties both are labeled as custom formatted.

I have repeatedly selected the column and set format to the proper DATE option instead of custom, but it keeps occuring. This causes orders to be set on the wrong dates or perceived wrong.

It is also only this file that does it AFAIK.

Who knows which setting or so that can cause this behavior and where to correct this?

Regards.

1
exceldateformat
Cell formatting only affects how data in that cell is displayed. The control over how the input is parsed is based on the Windows Regional Settings. If they do not agree, you can see results such as you show. - Ron Rosenfeld
@RonRosenfeld that is clear. But the question is. How do i change that behavior to the one i want PERMANENTLY, as it keeps reoccurring every time i open the file. - Bart
Are you certain the cell format in the workbook is really dmy and not General? If it is, try formatting the cells as [$-nl-BE]dd/mm/jjjj (assuming your WRS are also for Belgium Dutch.) - Ron Rosenfeld
I keep setting it to the same setting as in an other file, by opening cell properties. but every new entry goes wrong. And this for the whole column. Also iirc it didn't do that in the beginning. It's a named range. Can that have something to do with it? - Bart
Named range should have nothing to do with it. What is the numberformat for the cell that is changing? (saying it is the same as some other computer doesn't tell me what it is). What are your Windows Regional Settings on the computer where this changes. - Ron Rosenfeld

1 Answers

0
votes

Seems to work: question answered.

To fix it:

  1. Select all the cell in the named range portion of the column (and nothing else).
  2. Then custom format all the cells to dd/mm/yyyy or the format needed.

By Ron Rosenfeld