GETTING MONTH FROM A DATE

  • Thread starter undergrowthseer
  • Start date
U

undergrowthseer

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!
 
H

Harald Staff

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
 
U

undergrowthseer

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 .
 
U

undergrowthseer

ei harald,

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

Harald Staff

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
 
D

Dave Peterson

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.

[email protected] said:
ei harald,

tried what you said, but still displays the whole date [with the day]
Click to expand...
 
U

undergrowthseer

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

undergrowthseer

ei dave,


is the first formula you provided :

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


just makes the value a text and i wont be able to compare it with other
dates?

coz that seems to be the problem coming up when i run it, i made a
simple macro so i can test if it's actually comparing dates.

Sub alkdfj()


Dim DateToday As String
DateToday = Format(Date, "mmm yyyy")
With Selection
.NumberFormat = "@" 'text
.Value = DateToday
End With
ActiveCell.Value = DateToday
Dim First As Date
Dim Second As Date
Selection.Value = DateToday
DateToday = First
Range("P2").Value = Second 'Value in Cell P2 = 09/09/2005
Second = Format(Date, "mmm yyyy")
ActiveCell.Offset(0, 1).Select

If First > Second Then
ActiveCell.Value = "Right"
Else
ActiveCell.Value = "Wrong"
End If

End Sub

I can't get it right. and in this syntax the value on the P2 becomes
"12:00:00 AM"


sorry, im kinda new with this =D
 
D

Dave Peterson

If you want to see "Jan 2005" in the formula bar, then you can't keep it a
date. If you want to see "Jan 2005" in the cell, but 01/01/2005 in the formula
bar, then keep it a date.

Jan 2004
will come after
Dec 2004
in a text comparison.
 
Top