Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007: @*. formatting | Excel Discussion (Misc queries) | |||
Conditional formatting of date-cell - 2007 | Excel Discussion (Misc queries) | |||
How to convert Gregorian date into Hijri Date in Excel 2007? | Excel Discussion (Misc queries) | |||
Formatting in Excel 2007 | Excel Discussion (Misc queries) | |||
Formatting in Excel 2007 | New Users to Excel |