Date formats don't retain their format?

Hello everyone!

I select a range of cells with dates in them and I set their formats to types like this "14/03/01".

But this is the format I want: Day/Month/Year >>> 11/02/2018

But no matter what I try some cells display correctly and some just won't ???

I don't know what else to try anymore!!!

Please view image... as you can see that there is some code writing dates in the range of date cells and again some will display correctlyt and some display in the non desired date format???

Any help would be greatly appreciated!

Rn

Cant really see the code, the pic is a little small.

But try Custom format dd/mm/yy;@

In the Watch window, all of the values in NoOfClaimsPerWeek are surrounded by double quotes -- suggesting they are text strings and not real numbers/dates. I cannot test what happens when VBA writes a "date stored as text" into an Excel cell, but I notice that all of your dates in the picture spreadsheet cells are left justified -- default for text string where numbers (and dates are really just numbers) default to right justified.

I might test this with an ISTEXT() function. Enter =ISTEXT(X6) into X14 and copy down and across. This will tell you which of those dates (if any) are text strings and which are real date serial numbers.

Assuming that at least some of those dates are really text, the solution I see is to go back to the either the point where the data are read into NoClaimsPerWeek and make sure the date "field" is entered into VBA as a date serial number or go back to where the date string is written to Excel and make certain that it is converted to the correct date serial number when written to the spreadsheet. If the dates are entered into the cell as proper date serial numbers, then they will respond to the desired number format.

Thanks for the feedback