Remove date format but retain values

[scorziello]

I am working on a project that pulls data from a web connection that has date variables in it. I have figured out how to edit the variable in the url. I wanted to use a date picker to allow the user to select the date. The issue I am having is that encodeurl will only work on cells formatted as either general or text. I have yet to find a way to change the format of the date but retain the values. For instance the date picker enters the date of 06/11/2021, encodeurl sees this as 44358. I am open to referencing the cell in another cell, I am just completely stumped as to how to make this work.

 

[scorziello]

Update. After a bit of trial and error I came across this snippet

Sub Test()
    Dim Cell As Range
    With ActiveSheet
        .Range("A1").Copy .Range("B1")
        .Range("B1").NumberFormat = "yyyy/mm/dd"
        For Each Cell In Range("B1")
            Cell.Value = Cell.Text
        Next Cell
    End With
End Sub

Using this code I am able to use the encodeurl on the cell with the converted data and it works with random numbers in A1, i.e. 44/21/221 returns 44%2F21%2F2212. However when I enter an actual date in A1, the encodeurl function in C1 returns the 5 digit excel date.

 

[Akuini]

Hi, @scorziello. Welcome to the Forum.

The issue I am having is that encodeurl will only work on cells formatted as either general or text.

Try this:
Format col A as Date, col B as Text.
Then run this code:

Sub try222()
Range("B1") = Range("A1").Text
End Sub

OR

Sub try111()

For Each c In Range("A1:A10")
c.Offset(, 1) = c.Text
Next

End Sub