Date Formatting

I have an interesting problem. I have column "E" on my spreadsheet that shows
all cells with a comment tick with the information, 'Text Date with a 2-digit
Year'. But when I try to programmatically correct this with a command like -

ActiveSheet.Columns("E").NumberFormat = "mm/dd/yyyy"

The format doesn't change my dates to reflect a four year digit. It will
only change if I format another cell that has no comment tick and actually
type in a date. Even if I copy and paste special-values into the other cell,
the format remain in the 2-digit year.

Anyone with a fix?

Hello Langron,

Remove the tick marks and your code should work fine.

Sincerely,
Leith Ross

Langron

I doubt that the comment has anything to do with it - although what the
comment says probably does. I think you have text in the cells that looks
like a date, not an actual date. No matter how you change the number
format, the textdate won't change because it's text and not a number.

In the UI, after you change the format, hit F2 and enter to convert the text
to a number. In code, it would be

Range("E1").NumberFormat = "mm/dd/yyyy"
Range("E1").Value = Range("E1").Value

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

langron wrote:
> I have an interesting problem. I have column "E" on my spreadsheet
> that shows all cells with a comment tick with the information, 'Text
> Date with a 2-digit Year'. But when I try to programmatically correct
> this with a command like -
>
> ActiveSheet.Columns("E").NumberFormat = "mm/dd/yyyy"
>
> The format doesn't change my dates to reflect a four year digit. It
> will only change if I format another cell that has no comment tick
> and actually type in a date. Even if I copy and paste special-values
> into the other cell, the format remain in the 2-digit year.
>
> Anyone with a fix?