Possible IF code for Date ??

[ipbr21054]

Hi,
For some time now & still unable to find the cause im asking if there is a vba code etc for which will check & alter the date.

The worksheet is called DATABASE
The date will always be in column N
The first row this applies to is Row 5

My issue is sometimes i paste or typr the day like so into the cell 09/02/2023
For some reason its changed to 02/09/2023

Its not always noticed at first but then sometime down the road when checking a customers file then its noticed.
Example.
My database shows the part being out of warranty but when the customer shows me the receipt the date is different.
Looking on the databse then confirms he is correct & my date in the column is wrong.

Hence why a code that if the date in column N is MM/DD/YYY then change it to DD/MM/YYY

Thanks please advise

 

[awoohaw]

Try clearing all formatting and enter the same date in different formats to see what is displayed when you hit enter?
Enter as Sept 2 2023, 9/2/2023 2-Sep-2023 in Column A, and so forth. should know these are dates and covert them into the date serial number, and the formula bar will display the date in the regional format.

Additionally, in an adjacent cell in column you can put a formula of =A# and copy down Change the format to General for all of these cells and compare the date values (they should all be equal).

Also, check the regional settings on your computer. The regional settings are the default that excel uses. You cannot change the default You can alway change the formats via the formatting menu.

I'm not sure how much this will help, but it should give you an understanding of how dates work in excel with your regional settings.

Here is that exercise done on my computer;

Mr Excel Questions 3.xlsm
	A	B	C	D
1	Input	Cell Display	Formula Bar Display	date value
2	input: 9/2/2023	09/02/2023	09/02/2023	45171
3	input 2 Sep 2023	09/02/2023	09/02/2023	45171
4	input: Sept 2, 2023	02-Sep-23	09/02/2023	45171
Sheet17

Cell Formulas
Range	Formula
D2:D4	D2	=B2

 

[Micron]

Entered dates are interpreted by Regional Settings in Windows. If you and customer use different settings, you'll have to format your dates to agree with customer settings.

 

[ipbr21054]

Regional date setting dates are fine.

I enter 02/04/2023 into the cell & as soon as I leave I see it change to 04/02/2023

So as I see it change & to get the correct date I need like today I need to enter it like 02/10/2923 knowing it will change to 10/02/2023

But this is getting to be a pain so know I need code to help me just keep the dates as DD/MM/YYYY

 

[awoohaw]

when you enter 2/4/2023 were you trying to enter 2-Apr-2023?
What happens when you type the date in long form?
and Look at the formula bar.

 

[ipbr21054]

Will need to check tomorrow.
Answer to question.

I’m saying if today is 2nd April 2023 I enter 02/04/2023

I leave the cell & see it 04/02/2023 of which is 4th February 2023

 

[awoohaw]

okay. I'm pretty sure your regional setting is MM/DD/YYYY.

 

[ipbr21054]

Hi
I have checked & it’s DD/MM/YYYY

 

[ipbr21054]

Hi
I have checked & it’s DD/MM/YYYY

That was the first placed I checked.

 

[awoohaw]

And, it shows mm/dd/yyyy in the formula bar with general formatting?

Well, that is strange. I'm sorry you are having that issue. I hope someone can chime in.