Cell Format Difference

[AlexanderBB]

A Column in my worksheet holds dates
Format Cells - Number - Category is set to Custom and Type is dd mmm yyyy

Most of the entries in this column display like this in the Formula bar, or if read with VBA.
But some of them are in format dd/mm/yyyy.

All are dd mmm yyyy looking at the worksheet and right click "Format Cells" settings are the same.
Is there a reason for the difference and any suggestions for a 'fix' ? Thank you.

 

[StephenCrump]

Have a close look at what you actually have in one of the cells that appears as dd/mm/yyyy.

If you have today's date 16/10/2024 in a cell, i.e. the number 45581, it will have LEN = 5. You can format this date (i.e. number) to display as dd mmm yyyy.

But if you have the text value '16/10/2024 in a cell, it will have LEN = 10 (or more, if there are space or other characters in the string) and it won't be affected by any number formatting you apply.

The "fix" will depend on what you have in these cells.

 

[AlexanderBB]

> it won't be affected by any number formatting you apply.
This is the what right-Click -Format Cells shows ? Has no effect ? Unless what's there is a number like 45581?
I didn't know that and it 'seemed' to work for the first 1500 rows,
Looking at the worksheet visually I see dd mmm yyyy which is what's wanted.

Debug.Print Len(Range("V1500")), Range("V1500")
 Debug.Print Len(Range("V1501")), Range("V1501")

11 08 Sep 1960
10 30/07/1960
Does that suggest the fix needed ? I'm a bit lost here, can you rely on the formula bar to show what's really there? So far it's always the 2 formats above, no 5 digit number.

 

[Alex Blakenburg]

It means you have the dates entered as Text and not Dates. How are you getting the dates into those cells ?
If you are using code show us the code.

 

[StephenCrump]

You could try using the DATEVALUE function to convert both text types to dates.

But I'd be suspicious about the accuracy of the data, and agree with Alex - focus on getting the dates into the cells correctly in the first place.

 

[AlexanderBB]

It would be a major change to convert all to a date type. There's 3 per row and they do get verified against other data in the row,
Some were typed in, most are now from an Access recordset

 Select Case rx(index).Type
                    Case 7 'Date
                        v = Format(rx(index), "dd mmm yyyy")

Rather then a design change, altering those affected would be preferred. E.g. the sheet has 18000 rows buit only about 40 have this issue.
But deleting and typing them in again sees them remain the same.
What I'd like to know is what causes the differnce.

 

[StephenCrump]

It would be a major change to convert all to a date type.

It would be easier in Excel working with numeric. But let's take it that you want text values for all dates, in the format dd mmm yyyy. And you incorrectly have some text values in the format dd/mm/yyyy.

The code snippet you've posted doesn't show how the cells are being populated.

But if the dates are text values, setting the number format will have no effect on the display - hence your observations in Post #1.

It sounds like you have applied text and numeric formats, and retyped some values, so you probably have a mismash of formats.

If you format all your date cells as text, and retype 30 Jul 1960 instead of 30/07/1960, than it should appear correctly as the text value 30 Jul 1960 with length 11?

 

[AlexanderBB]

>The code snippet you've posted doesn't show how the cells are being populated.
The next bit is Cells(row, col) = v
>If you format all your date cells as text, and retype 30 Jul 1960 instead of 30/07/1960, than it should appear correctly as the text value 30 Jul 1960 with length 11?

Contemplating a change, for a test I cleared a cell, changed it's format to Date and entered 45581. It shows in the Formula Bar as 16/10/2024 but in the cell as 16 October 2024.
That's messy. Your suggestion above is neater and keeps the same format in the cell and Formula bar.
Are there any considerations changing from Custom to Text?

 

[AlexanderBB]

Sub CustomToText()
Dim irange As Range
Dim cl As Range
Dim dt As String
Set irange = Range("V2:V18609")
For Each cl In irange
    dt = cl.Value
    cl.ClearContents
    cl.NumberFormat = "@"
    cl.Value = Format(dt, "dd mmm yyyy")
Next
End Sub

That would be ok ? I'll have to test some date handling code and see if anything's changed.

 

[AlexanderBB]

If cl.NumberFormat = "dd mmm yyyy" as added before Next in the Msg 9 code, that fixes the original issue in msg 1.