Convert Date

[Ranjit kurian]

From the below code iam trying to compare my active cell with previous date
, if the active cell is less than previous month then the result need to be
Yes, else No.

The problem iam facing from the below code in variable mmyy, when i run
macro this variable is shown as Text inspite of Date because of which its not
able to compare with previous date.

Example: my date column is copied from MSAccess so the date is in exact this
formate SEP-08

sub test()
PrvMth = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmyy")
ActiveCell.Offset(1, 0).Select
a = ActiveCell
m = DatePart("m", a)
If (m < 10) Then
m = 0 & m
End If
y = Right(a, 2)
mmyy = m & y
MsgBox mmyy & vbLf & PrvMth
If mmyy < PrvMth Then
ActiveCell.Offset(0, 1) = "Yes"
Else
ActiveCell.Offset(-1, 0).Select
End If
End If
End If
End sub

 

[Rick Rothstein]

You can get the previous month from the Date function using the Month
function...

PreviousMonth = Month(Date) - 1

And assuming the ActiveCell contains a real date, you can get its month the
same way...

MonthForActiveCell = Month(Range(ActiveCell.Value))

Since both of these will be numbers, you can compare them directly...

If MonthForActiveCell < PreviousMonth Then

 

[Ranjit kurian]

Hi Rick,

Thanks for the reply, but the below code "MonthForActiveCell =
Month(Range(ActiveCell.Value))" was not wroking for me, so when i removed the
Range from the code "mmyy = Month(ActiveCell.Value) & Year(ActiveCell.Value)"
its working fine, but if my date is as of Dec-2008 it will show it as 122009,
pls advise ...

 

[Rick Rothstein]

Yes, the I added the Range call by mistake. I'll show you how to get the
mmyy value you want, but first I want to reiterate that you don't need that
in order to test if...

MonthForActiveCell < PreviousMonth

the If..Then test I gave you will do that test (once the erroneous Range
call is removed)...

PreviousMonth = Month(Date) - 1
MonthForActiveCell = Month(ActiveCell.Value)
If MonthForActiveCell < PreviousMonth Then
' Put "active cell date less than previous month" code here
End If

Okay, just in case you want to do it the way you started out...

mmyyy = Format(Month(ActiveCell.Value), "00") & _
Format(ActiveCell.Value, "yy")