VBA in Excel 2007 date formatting

[mccann_mj]

I'm trying to format the cells [1,16] and [1,17] to the "mmm-yy" format. The
user enters the date as "mm/dd/yy" into ADD-REP_DATE and ADD_PRD. I've tried
putting the last two statements above as well, to no effect: the dates still
show up in the cells with the "mm/dd/yy" format. I recorded a macro to change
the format, and came up with {Selection.NumberFormat ="[$-409]mmm-yy;@"}.
Cutting and pasting that didn't work either. The rest of the worksheet uses
the date in the "mmm-yy" format (so does the user).

NEC_SELECT.Offset(I, 0).Cells(1, 16) = Me.ADD_REP_DATE
NEC_SELECT.Offset(I, 0).Cells(1, 17) = Me.ADD_PRD
NEC_SELECT.Offset(I, 0).Cells(1, 16).NumberFormat = "mmm-yy"
NEC_SELECT.Offset(I, 0).Cells(1, 17).NumberFormat = "mmm-yy"

 

[OssieMac]

Hi Michael,

I am assuming that Me.ADD_REP_DATE is a text box and therefore would suggest
that the dates are in text format. Try the following.

NEC_SELECT.Offset(I, 0).Cells(1, 16) = DateValue(Me.ADD_REP_DATE)
NEC_SELECT.Offset(I, 0).Cells(1, 17) = DateValue(Me.ADD_PRD)
NEC_SELECT.Offset(I, 0).Cells(1, 16).NumberFormat = "mmm-yy"
NEC_SELECT.Offset(I, 0).Cells(1, 17).NumberFormat = "mmm-yy"

 

[mccann_mj]

Thank you OssieMac! Glad to have somebody to remind me of the basics. And
yes, it worked.
--
Michael JM

Hi Michael,

I am assuming that Me.ADD_REP_DATE is a text box and therefore would suggest
that the dates are in text format. Try the following.

NEC_SELECT.Offset(I, 0).Cells(1, 16) = DateValue(Me.ADD_REP_DATE)
NEC_SELECT.Offset(I, 0).Cells(1, 17) = DateValue(Me.ADD_PRD)
NEC_SELECT.Offset(I, 0).Cells(1, 16).NumberFormat = "mmm-yy"
NEC_SELECT.Offset(I, 0).Cells(1, 17).NumberFormat = "mmm-yy"


--
Regards,

OssieMac

I'm trying to format the cells [1,16] and [1,17] to the "mmm-yy" format. The
user enters the date as "mm/dd/yy" into ADD-REP_DATE and ADD_PRD. I've tried
putting the last two statements above as well, to no effect: the dates still
show up in the cells with the "mm/dd/yy" format. I recorded a macro to change
the format, and came up with {Selection.NumberFormat ="[$-409]mmm-yy;@"}.
Cutting and pasting that didn't work either. The rest of the worksheet uses
the date in the "mmm-yy" format (so does the user).

NEC_SELECT.Offset(I, 0).Cells(1, 16) = Me.ADD_REP_DATE
NEC_SELECT.Offset(I, 0).Cells(1, 17) = Me.ADD_PRD
NEC_SELECT.Offset(I, 0).Cells(1, 16).NumberFormat = "mmm-yy"
NEC_SELECT.Offset(I, 0).Cells(1, 17).NumberFormat = "mmm-yy"