Date changes on worksheet

[ipbr21054]

Morning,

Headers are in row 4
Row 5 is hidden
Column M is for date.
My database range is A-W then down the page.
A new row is created in row 6 & i then start to complete each cell,some i type but some have drop down list.

Im finding that when i enter the date the month & day reverse when i leave the cell.
So this morning i have typed in cell M6 02/06/2020 i leave the cell to see it change to 06/02/2020
As a test i unhide row 5 and type the same date in cell M5 but when i leave the cell it stays the same.

Checking the format for cell M5 is shown in screenshot below.

I cant afford to have dates change like its doing so can you advise a fix or another approach for the date.

Thanks

 

[jasonb75]

Check the cell format, possibly some cells are formatted for UK dates (dd/mm/yyyy) and others for US dates (mm/dd/yyyy).

If the formats are the same on both cells then the only other explanation would be that something in your vba code is changing it.

 

[ipbr21054]

Hi,
The cells are shown as in screenshot above dd/mm/yyyy

Is there a way to find out if a code is working on specific cells

 

[jasonb75]

Have you checked the format on each individual cell? Selecting multiple cells will only show you the format of one cell, even if the others are different.

Unless you passed the date from a userform, it would most likely be in a Worksheet_Change procedure in the module of the affected sheet.

 

[ipbr21054]

Yes each separate cell.

The format for the hidden row & row 6 is the same BUT one date changed where the other does nothing.
Not passed from userform

 

[jasonb75]

Worksheet Change code?

 

[ipbr21054]

Nope just checked.

My range for the working area is A-W so with a specific cell in question M6 i watch the date change whether its formatted *02/06/2020 or 02/06/2020
So i try outside of my range say AQ6 i enter the date 02/06/2020 & leave the cell & it stays as typed.
I go to cell M6 & watch it change eacvh time.
If i format the cell M6 to TEXT & type 02/06/2020 when i leave the cell it stays as typed

 

[mole999]

what is the os default date settings

 

[jasonb75]

whether its formatted *02/06/2020 or 02/06/2020

Those are not formats, they are examples which could be either June 2nd or Feb 6th depending on the actual format.

I don't think that you have properly checked that the format is correct, you've only looked at the appearance of the example.

Click on M5, go to the formatting and click 'Custom', then grab a screen capture. Do the same for M6, then post both captures.

 

[ipbr21054]

I did check that but i asked myself why only specific cells does it changed ?