VBA Range Format

[Fchedraoui]

VBA,

F2:F99999 = date format = 18-sept-21

"Range("F2:F99999").Numberformat = "yyyy-mm-dd"" works and format come as 2021-09-18 as custom

but then changes format after closing excel. when i reopen excel the format becomes date. and transfer program need string to transfer the data.

Format intended = custom format

Format after closing and reopening excel = Date

how to fix this and why this is happening?

[#2]

@Fchedraoui 

yyyy-mm-dd is a Date format. Applying does not change the values to text, it only changes the way they are displayed. You might populate another column with text values:

    With Range("G2:G99999")
        .Formula = "=IF(F2="""","""",""'""&TEXT(F2,""yyy-mm-dd""))"
        .Value = .Value
    End With

[#3]

Here is the result:
'2021-03-14
I cannot have ' in the beginning, is there a work around?
the funny part is if i go to excel and i select all data except title of the F column and then right click format custom and choose yyyy-mm-dd it works no problem. it stays as custom. 

[#4]

@Fchedraoui 

How about

    With Range("G2:G99999")
        .Formula = "=IF(F2="""","""",TEXT(F2,""yyy-mm-dd""))"
        .NumberFormat = "@"
        .Value = .Value
    End With

[#5]

@Fchedraoui 

I am guessing somewhat, but I suspect the problem is that the column contains datevalues and though applying a number format creates the correct appearance in the range, it is still a number and will be read as such.  My knowledge of VBA is somewhat sketchy, but maybe something of the nature

Option Explicit
Option Base 1

Sub ToText()
'
' ToText Macro
'
Dim v As Variant
Dim i As Long, n As Long
Dim t() As Variant
Dim rng As Range
'
    Set rng = Range("dates")
    v = rng.Value
    n = UBound(v, 1)
    ReDim t(n, 1)
    For i = 1 To n
        t(i, 1) = "'" & Application.WorksheetFunction.Text(v(i, 1), "yyyy-mm-dd")
    Next i
    rng.Value = t

End Sub

might provide a start.

[#6]

I tested it in a sheet and it seemed to work fine. I tested inside a table, inside a spill range, regular values and it saved the custom format in every case.
Since it is a macro enabled file (I assume) is it possible there is some other macro that runs on opening/closing/etc... that is overriding the custom formatting?

[#7]

Sorry @Hans Vogelaar , still showed as no replies when I started.

@Fchedraoui  Are you sure about the apostrophe "'"?  Excel normally ignores it but, as Hans suggests the text number format "@" should also work.

[#8 · Accepted solution]

WAW nice! Bingo!

Explain.. :

.formula means what ?
the IF is an IF excel function? if so, then F2="""" is the logical test ? does it mean if empty? why 4 of "
then , """" means value if true? and then ,

TEXT(F2,""yyy-mm-dd"")  if value if false?

.Numberformat  = "@"  will format G column as TEXT

.value (G2) = .value (F2)   <-- how does it know this is F2 ?

end with (end loop) 

sorry for all the questions but i want to understand it.

Thank you so much

@Hans Vogelaar 

[#9]

@Peter Bartholomew  I had the same thing happen with it not showing the replies

@Hans Vogelaar  Glad you caught the need

@Fchedraoui  .formula will set what the 'formula' in that cell is set to.  

   The duplicate "" is basically escape character to tell excel to actually send a " instead of the " meaning the following characters are part of a string so 

"=IF(F2="""","""",TEXT(F2,""yyy-mm-dd""))"

 should look like

=IF(F2="","",TEXT(F2,"yyy-mm-dd"))

inside the cell on the sheet itself  

yes the .numberformat="@" is text

as for the .value=.value it is not G2 = F2 and I suspect this doesn't really do anything since it is really the .formula that matters.