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

 

[ipbr21054]

Try this please

DR CUT DOWN.zip

drive.google.com

 

[mole999]

only thing i can see

With ThisWorkbook.Sheets("DATABASE")
    .Rows("53").Insert Shift:=xlDown
    .Range("B53:O53").Borders.Weight = xlThin
    .Range("M53").Value = Date
    .Range("O5").NumberFormat = "$#,##0.00"
    .Range("B53").Select
End With

in LINE

 

[jasonb75]

Finally tracked it to one line in your worksheet change code, the problem was being caused by cell = UCase(cell)
To fix the problem an earlier line needs to be changed so that the date column is excluded from the UCase conversion.

Set rng = Intersect(Target, Range("6:" & Rows.Count), Range("A:L,N:W"))

Not sure why UCase is changing the date, but it looks like this works.

 

[ipbr21054]

Well done & many thanks.
It was starting to be an issue.

strange why something that has nothing to do with it made it change.

Thanks for your time.

 

[jasonb75]

Probably because the cell is being manipulated by the UCase command even though there is nothing in the cell to convert, which it will always try to do unless it is explicitly instructed to ignore.
Add to that the anomalies that often seem to occur with vba and uk format dates, something inevitably has to go wrong somewhere