ei guys,

can anybody help me with this. I have this macros where it gets the
current date when it runs and gets the month and year only. Is this
possible? To just format the date and have it just show the month and
year? coz when use this:


Dim DateToday As String
DateToday = Date
Selection.Value = DateToday
Selection.NumberFormat = "mmm yyyy"
Selection.Formula = Selection.Value
ActiveCell.Offset(0, 1).Select

It just changes the value you'll see:

eg.

when you look at the worksheet you'll be able to see

"Sep 2005"

but when you select that cell it shows on the "bar"

"09/29/2005" [this is the exact date i run the macro]


can anyone help me pls...i need this by fri, and i really cant find any
solution on the net.



thanks man!

You declare as string and want a string, but assign a number and a
numberformat. Try

Dim DateToday As String
DateToday = Format(Date, "mmm yyyy")
Selection.Value = DateToday
ActiveCell.Offset(0, 1).Select


HTH. Best wishes Harald

skrev i melding
oups.com...
ei guys,

can anybody help me with this. I have this macros where it gets the
current date when it runs and gets the month and year only. Is this
possible? To just format the date and have it just show the month and
year? coz when use this:


Dim DateToday As String
DateToday = Date
Selection.Value = DateToday
Selection.NumberFormat = "mmm yyyy"
Selection.Formula = Selection.Value
ActiveCell.Offset(0, 1).Select

It just changes the value you'll see:

eg.

when you look at the worksheet you'll be able to see

"Sep 2005"

but when you select that cell it shows on the "bar"

"09/29/2005" [this is the exact date i run the macro]


can anyone help me pls...i need this by fri, and i really cant find any
solution on the net.



thanks man!

i may not have been clear. my question is how can i extract the month
and the year from the date value? i want to exclude the "day" from the
date coz im comparing months.

here's an example:

i have a row of data:

name date started date finished SEP05 OCT05 NOV05
DEC05 JAN06

NAME1 09/30/2004 11/15/2005 1 1
1 0 0

*1 means that he/she is still busy working on the project while 0 means
he doesnt have anymore to do.


now, i want my macro to plot [or put a mark] on the dates that he/she
has worked on a project [thus the date started and date finished comes
in]. It just basically compares the months of the date the person
started and compares it with the variables of the months to be plotted.
BUT, when i run the macro, it gets the present date, and the present
date consists of the dd-mm-yy, but i ONLY need the "mm-yy" is that
possible? or do i just have to convert it to serials and compare the
data thru that?


hope you understand, thanks for the time .

ei harald,

tried what you said, but still displays the whole date [with the day]

Ah. Does not here. Excel is very assisting when it comes to date entries,
whatever may be a date will be a date, and "may" depends on regional
settings and customs.

Here's a formula approach, try in a new empty sheet:

Enter first of each month in cells D1, E1, F1, ...rightwards as your header
row and format these as mmm yyyy.
Enter name in A2, start date (real date) in B2, end date in C2.
Formula in D2:
=($B2<=DATE(YEAR(D$1),MONTH(D$1)+1,0))*($C2=DATE( YEAR(D$1),MONTH(D$1),1))
fill D2 right and down in your grid.

HTH. Best wishes Harald

skrev i melding
oups.com...
ei harald,

tried what you said, but still displays the whole date [with the day]

Maybe something like this that treats the value as text--not a date:

Dim DateToday As String
DateToday = Format(Date, "mmm yyyy")
With Selection
.NumberFormat = "@" 'text
.Value = DateToday
End With
ActiveCell.Offset(0, 1).Select

Personally, I think I'd try to keep the value a date--but I'd use the 1st of
each month.

Dim DateToday As Date
DateToday = DateSerial(Year(Date), Month(Date), 1)
With Selection
.NumberFormat = "mmm yyyy"
.Value = DateToday
End With
ActiveCell.Offset(0, 1).Select

You can still compare cells, but you can also do date arithmetic if you have to.

" wrote:

ei harald,

tried what you said, but still displays the whole date [with the day]

--

Dave Peterson

sorry for the late reply guys, ill be trying that out today, ill give
my feedback asap. thanks man =D