vba - set data in cell to what date is being displ

Question

Probably a simple question, I just cannot pinpoint my figure on it as I still do not fully understand how to approach the different "layers" in excel (what the data actually is in a cell compared to what is being displayed...Ex. date)

I have the following data inside of my cells

10/17/2014
10/18/2014
10/19/2014
10/20/2014

I ran the following vba code to hopefully reformat that data:

Sheet1.Range("C2", "C1000").NumberFormat = "dd-mmm-yyyy"

This makes my data appear as:

17-Oct-2014
18-Oct-2014
19-Oct-2014
20-Oct-2014

Which is actually what I want to be. Only problem is if I click inside of the actual cell, the data is still in 10/18/2014 format.

Now from my understanding "format" will just change how the data is displayed. I cannot seem to find online how to take what is displaying on the screen and change the value of the cell to what is being displayed. I would think this is a "popular" issue and asked repeatedly so perhaps my search skills are just off and I couldn't locate the article. Any help would be appreciated. Thank you.

I have read that I could use "Paste Special", but this requires me to format cells and then copy and paste the values manually which is not what I would like. I would like to hopefully completely automate this inside of VBA.

Answer 1

Try this:

Sheet1.Range("C1:C1000").Value = Sheet1.Evaluate("""'"" & INDEX(TEXT(C1:C1000,""dd-mmm-yyyy""),)")

The ' at the beginning forces the output into text.

You could also do it this way:

Sheet1.Range("C1:C1000").NumberFormat = "@"
Sheet1.Range("C1:C1000").Value = Sheet1.Evaluate("INDEX(TEXT(C1:C1000,""dd-mmm-yyyy""),)")

This will turn numbers that are formatted as dates to text that look like dates. In other words if any reference to or mathematical formulas on these cells will need to covert them back to numbers.