Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Date
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Date
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 -- Rick (MVP - Excel) "Ranjit kurian" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Date
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" wrote: 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 -- Rick (MVP - Excel) "Ranjit kurian" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Date
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") -- Rick (MVP - Excel) "Ranjit kurian" wrote in message ... 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" wrote: 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 -- Rick (MVP - Excel) "Ranjit kurian" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert US date with 12hr format to European date 24hr | Excel Discussion (Misc queries) | |||
Is it possible to convert solar date to lunar date using excel? | Excel Discussion (Misc queries) | |||
How to convert Gregorian date into Hijri Date in Excel 2007? | Excel Discussion (Misc queries) | |||
How do you convert an uncoded date (e.g., 20080304) to a date form | Excel Worksheet Functions | |||
Convert a julian gregorian date code into a regular date | Excel Worksheet Functions |