VBA in Excel 2007 date formatting
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" -- Michael JM |
VBA in Excel 2007 date formatting
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 "mccann_mj" wrote: 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" -- Michael JM |
VBA in Excel 2007 date formatting
Thank you OssieMac! Glad to have somebody to remind me of the basics. And
yes, it worked. -- Michael JM "OssieMac" wrote: 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 "mccann_mj" wrote: 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" -- Michael JM |
All times are GMT +1. The time now is 05:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com