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?

Date Formatting
 

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

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-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?