I have created a user form which automatically enters the current date by
using the following code in the UserForm Initialize.
txtDate.Value = Now()
When the information is put in the spreadsheet it shows the date in one cell
like this:
4/9/2006 19:33
I would like to format the date in vba within the form so that it returns
only the month and year, leaving out the day and time. I don't want to do
this by formatting the column in the worksheet because I will need to
manipulate the date in formulas.

You can instead use:
txtDate.Value = Date

However, as long as you want the value to be a "date", there is no way to
get rid of the "day". (Otherwise, it's not a date! A date contains "year",
"month", and "day", three components.) You can at most use cell format to
display only the month and year.

There should be other approaches which can be used in the "formulas" you
mentioned. Maybe you can share with us and I'm sure someone can help.

Regards,
Edwin Tam

http://www.vonixx.com


"juliejg1" wrote:

I have created a user form which automatically enters the current date by
using the following code in the UserForm Initialize.
txtDate.Value = Now()
When the information is put in the spreadsheet it shows the date in one cell
like this:
4/9/2006 19:33
I would like to format the date in vba within the form so that it returns
only the month and year, leaving out the day and time. I don't want to do
this by formatting the column in the worksheet because I will need to
manipulate the date in formulas.

txtDate.value = format(now(),"mmm yyyy")

--
Regards,
Tom Ogilvy


"juliejg1" wrote:

I have created a user form which automatically enters the current date by
using the following code in the UserForm Initialize.
txtDate.Value = Now()
When the information is put in the spreadsheet it shows the date in one cell
like this:
4/9/2006 19:33
I would like to format the date in vba within the form so that it returns
only the month and year, leaving out the day and time. I don't want to do
this by formatting the column in the worksheet because I will need to
manipulate the date in formulas.

Thanks!

"Tom Ogilvy" wrote:

txtDate.value = format(now(),"mmm yyyy")

--
Regards,
Tom Ogilvy


"juliejg1" wrote:

I have created a user form which automatically enters the current date by
using the following code in the UserForm Initialize.
txtDate.Value = Now()
When the information is put in the spreadsheet it shows the date in one cell
like this:
4/9/2006 19:33
I would like to format the date in vba within the form so that it returns
only the month and year, leaving out the day and time. I don't want to do
this by formatting the column in the worksheet because I will need to
manipulate the date in formulas.

Thanks1

"Edwin Tam" wrote:

You can instead use:
txtDate.Value = Date

However, as long as you want the value to be a "date", there is no way to
get rid of the "day". (Otherwise, it's not a date! A date contains "year",
"month", and "day", three components.) You can at most use cell format to
display only the month and year.

There should be other approaches which can be used in the "formulas" you
mentioned. Maybe you can share with us and I'm sure someone can help.

Regards,
Edwin Tam

http://www.vonixx.com


"juliejg1" wrote:

I have created a user form which automatically enters the current date by
using the following code in the UserForm Initialize.
txtDate.Value = Now()
When the information is put in the spreadsheet it shows the date in one cell
like this:
4/9/2006 19:33
I would like to format the date in vba within the form so that it returns
only the month and year, leaving out the day and time. I don't want to do
this by formatting the column in the worksheet because I will need to
manipulate the date in formulas.

But note that you still cannot actually get rid of the "day". By default
Excel just put the 1st day of the month for you. For example, 1st of Apr
2006. There is absolutely no way you can get rid of the day. The effect of
that statement is identical to formatting the cell, which you specifically
mentioned you don't want to do.

Regards,
Edwin Tam

http://www.vonixx.com


"juliejg1" wrote:

Thanks!

"Tom Ogilvy" wrote:

txtDate.value = format(now(),"mmm yyyy")

--
Regards,
Tom Ogilvy


"juliejg1" wrote:

I have created a user form which automatically enters the current date by
using the following code in the UserForm Initialize.
txtDate.Value = Now()
When the information is put in the spreadsheet it shows the date in one cell
like this:
4/9/2006 19:33
I would like to format the date in vba within the form so that it returns
only the month and year, leaving out the day and time. I don't want to do
this by formatting the column in the worksheet because I will need to
manipulate the date in formulas.

Actually, format returns a string, so what you see is what you get. There is
no day associated with it as it is no longer a date (if I recall
correctly)...
--
HTH...

Jim Thomlinson


"Edwin Tam" wrote:

But note that you still cannot actually get rid of the "day". By default
Excel just put the 1st day of the month for you. For example, 1st of Apr
2006. There is absolutely no way you can get rid of the day. The effect of
that statement is identical to formatting the cell, which you specifically
mentioned you don't want to do.

Regards,
Edwin Tam

http://www.vonixx.com


"juliejg1" wrote:

Thanks!

"Tom Ogilvy" wrote:

txtDate.value = format(now(),"mmm yyyy")

--
Regards,
Tom Ogilvy


"juliejg1" wrote:

I have created a user form which automatically enters the current date by
using the following code in the UserForm Initialize.
txtDate.Value = Now()
When the information is put in the spreadsheet it shows the date in one cell
like this:
4/9/2006 19:33
I would like to format the date in vba within the form so that it returns
only the month and year, leaving out the day and time. I don't want to do
this by formatting the column in the worksheet because I will need to
manipulate the date in formulas.

In the original question, the sentence "When the information is put in the
spreadsheet..." implied that the value is to be placed in a cell in the
worksheet.

Therefore, if you try to experiment in the immediate window,
selection.value = format(now(),"mmm yyyy")

You can see that Excel actually put the 1st of April 2006 into the cell. It
is not a string at all, and the statement does not at all instruct Excel to
return a string.

Regards,
Edwin Tam

http://www.vonixx.com



"Jim Thomlinson" wrote:

Actually, format returns a string, so what you see is what you get. There is
no day associated with it as it is no longer a date (if I recall
correctly)...
--
HTH...

Jim Thomlinson


"Edwin Tam" wrote:

But note that you still cannot actually get rid of the "day". By default
Excel just put the 1st day of the month for you. For example, 1st of Apr
2006. There is absolutely no way you can get rid of the day. The effect of
that statement is identical to formatting the cell, which you specifically
mentioned you don't want to do.

Regards,
Edwin Tam

http://www.vonixx.com


"juliejg1" wrote:

Thanks!

"Tom Ogilvy" wrote:

txtDate.value = format(now(),"mmm yyyy")

--
Regards,
Tom Ogilvy


"juliejg1" wrote:

I have created a user form which automatically enters the current date by
using the following code in the UserForm Initialize.
txtDate.Value = Now()
When the information is put in the spreadsheet it shows the date in one cell
like this:
4/9/2006 19:33
I would like to format the date in vba within the form so that it returns
only the month and year, leaving out the day and time. I don't want to do
this by formatting the column in the worksheet because I will need to
manipulate the date in formulas.