VBA DateValue help

[ebilbrough]

Fairly new with VBA. In cell range D2:AC2 I have dates in a text format (ex. 1/2/2017).

Looking for a way in VBA to convert the range to date format (mm/dd/yy).

Thanks.

 

[JeanKim]

Range("D2:AC2").select
Selection.numberformat = "mm/dd/yyyy"

 

[ebilbrough]

Thanks JeanKim. It converted the cells to the custom date format (mm/dd/yy) but it didn't change how the dates are displayed.

 

[JeanKim]

Thanks JeanKim. It converted the cells to the custom date format (mm/dd/yy) but it didn't change how the dates are displayed.

what was it before and what is it after the code changed format? also your example 1/2/2017 <---which number is month and which is date?

 

[ebilbrough]

Hi JeanKim - 1 is the month - January 2, 2017. The date display doesn't change. It remains 1/2/2017. Only the cell formatting changed - from General to Custom.

 

[ebilbrough]

Also, just noticed if I go to edit the cell (F2 - but make no edits) the display of the date changes to the desired format.

Is there any way to have this occur automatically within the VBA code?

 

[JeanKim]

it should change display to format "mm/dd/yyyy"
I tested on my computer and it worked.
I don't know what the problem is on your computer.