variable for Date

[Ranjit kurian]

I would like to give a variable for my date column, can some one advise me

Example:
I have column in this formate mmm/yy, my below variable should identify the
Month & Year, i need to variables one is for current month(Jan/09), and the
other for Previous Month(Dec/08)

CurrMth = Month(Today()) & Year(Today())
PrvMth = Month(Today()) - 1 & Year(Today())

 

[OssieMac]

I am not sure if you want to work in VBA code or in worksheet formulas
because TODAY() is a worksheet formula and your CurrMth and PrevMth equals is
VBA.

In worksheet formulas the following returns the values you want.

Current month and year
= MONTH(TODAY()) & " " & YEAR(TODAY())

Previous month and year
=MONTH(EDATE(TODAY(),-1)) & " " & YEAR(EDATE(TODAY(),-1))

In VBA. [Note that Date replaces TODAY()]

CurrMth = Month(Date) & " " & Year(Date)

PrevMth = Month(WorksheetFunction.EDate(Date, -1)) _
& " " & Year(WorksheetFunction.EDate(Date, -1))

However, WorksheetFunction.EDate does not work in VBA in XL2002. Not sure
about xl2003 but it does work in xl2007.

 

[OssieMac]

Some more answers that might be closer to what you want.

On worksheet
Current Month and Year

=TEXT(TODAY(),"mmm/yy")

Previous Month and Year

=TEXT(EDATE(TODAY(),-1),"mmm/yy")

in VBA

CurrMth = Format(Date, "mmm/yy")
PrevMth = Format(WorksheetFunction.EDate(Date, -1), "mmm/yy")

Note again that WorksheetFunction.EDate does not work in VBA in some earlier
versions of XL.

--
Regards,

OssieMac

I am not sure if you want to work in VBA code or in worksheet formulas
because TODAY() is a worksheet formula and your CurrMth and PrevMth equals is
VBA.

In worksheet formulas the following returns the values you want.

Current month and year
= MONTH(TODAY()) & " " & YEAR(TODAY())

Previous month and year
=MONTH(EDATE(TODAY(),-1)) & " " & YEAR(EDATE(TODAY(),-1))

In VBA. [Note that Date replaces TODAY()]

CurrMth = Month(Date) & " " & Year(Date)

PrevMth = Month(WorksheetFunction.EDate(Date, -1)) _
& " " & Year(WorksheetFunction.EDate(Date, -1))

However, WorksheetFunction.EDate does not work in VBA in XL2002. Not sure
about xl2003 but it does work in xl2007.

--
Regards,

OssieMac

I would like to give a variable for my date column, can some one advise me

Example:
I have column in this formate mmm/yy, my below variable should identify the
Month & Year, i need to variables one is for current month(Jan/09), and the
other for Previous Month(Dec/08)

CurrMth = Month(Today()) & Year(Today())
PrvMth = Month(Today()) - 1 & Year(Today())

 

[got.sp4m]

The EDate worksheetfunction isn't available in XL2003 VBA, please use
this instead:

Dim CurrMth As String
CurrMth = Format$(DateAdd("m", -1, Date), "mmm/yy")

Note: "Date" returns the current date.
Also note: use "Format$" instead of "Format" when you are using the
returned value as a string, this version of the function is quicker.

best regards
Peder Schmedling

Some more answers that might be closer to what you want.

On worksheet
Current Month and Year

=TEXT(TODAY(),"mmm/yy")

Previous Month and Year

=TEXT(EDATE(TODAY(),-1),"mmm/yy")

in VBA

CurrMth = Format(Date, "mmm/yy")
PrevMth = Format(WorksheetFunction.EDate(Date, -1), "mmm/yy")

Note again that WorksheetFunction.EDate does not work in VBA in some earlier
versions of XL.

--
Regards,

OssieMac

I am not sure if you want to work in VBA code or in worksheet formulas
because TODAY() is a worksheet formula and your CurrMth and PrevMth equals is
VBA.

In worksheet formulas the following returns the values you want.

Current month and year
= MONTH(TODAY()) & " " & YEAR(TODAY())

Previous month and year
=MONTH(EDATE(TODAY(),-1)) & " " & YEAR(EDATE(TODAY(),-1))

In VBA. [Note that Date replaces TODAY()]

CurrMth = Month(Date) & " " & Year(Date)

PrevMth = Month(WorksheetFunction.EDate(Date, -1)) _
    & " " & Year(WorksheetFunction.EDate(Date, -1))

However, WorksheetFunction.EDate does not work in VBA in XL2002. Not sure
about xl2003 but it does work in xl2007.

 

[Ranjit kurian]

Iam working in VBA.

Iam getting an error(object doesn't support) for the previous month Code.

Iam using an excel VBA, in excel i have a column called as MyDate which
contains only months like Jan/09, Dec/08, Nov/08,Oct/08 etc... i have
changed the format manually to mmm/yy format, but when i go to each cell its
still in the format of m/d/yyyy, because of which the vba code is giving an
error to me, as my date are not fixed, i need to change the formate of excel
into mmm/yy

Iam using the variables to an IF funtions, if the CurrMth is Jan/09 then the
result should be Yes, if the PrevMth is Dec-08 the result should be AYes

Some more answers that might be closer to what you want.

On worksheet
Current Month and Year

=TEXT(TODAY(),"mmm/yy")

Previous Month and Year

=TEXT(EDATE(TODAY(),-1),"mmm/yy")

in VBA

CurrMth = Format(Date, "mmm/yy")
PrevMth = Format(WorksheetFunction.EDate(Date, -1), "mmm/yy")

Note again that WorksheetFunction.EDate does not work in VBA in some earlier
versions of XL.

--
Regards,

OssieMac

I am not sure if you want to work in VBA code or in worksheet formulas
because TODAY() is a worksheet formula and your CurrMth and PrevMth equals is
VBA.

In worksheet formulas the following returns the values you want.

Current month and year
= MONTH(TODAY()) & " " & YEAR(TODAY())

Previous month and year
=MONTH(EDATE(TODAY(),-1)) & " " & YEAR(EDATE(TODAY(),-1))

In VBA. [Note that Date replaces TODAY()]

CurrMth = Month(Date) & " " & Year(Date)

PrevMth = Month(WorksheetFunction.EDate(Date, -1)) _
& " " & Year(WorksheetFunction.EDate(Date, -1))

However, WorksheetFunction.EDate does not work in VBA in XL2002. Not sure
about xl2003 but it does work in xl2007.

--
Regards,

OssieMac

I would like to give a variable for my date column, can some one advise me

Example:
I have column in this formate mmm/yy, my below variable should identify the
Month & Year, i need to variables one is for current month(Jan/09), and the
other for Previous Month(Dec/08)

CurrMth = Month(Today()) & Year(Today())
PrvMth = Month(Today()) - 1 & Year(Today())

 

[OssieMac]

What version of Excel are you using?

--
Regards,

OssieMac

Iam working in VBA.

Iam getting an error(object doesn't support) for the previous month Code.

Iam using an excel VBA, in excel i have a column called as MyDate which
contains only months like Jan/09, Dec/08, Nov/08,Oct/08 etc... i have
changed the format manually to mmm/yy format, but when i go to each cell its
still in the format of m/d/yyyy, because of which the vba code is giving an
error to me, as my date are not fixed, i need to change the formate of excel
into mmm/yy

Iam using the variables to an IF funtions, if the CurrMth is Jan/09 then the
result should be Yes, if the PrevMth is Dec-08 the result should be AYes

Some more answers that might be closer to what you want.

On worksheet
Current Month and Year

=TEXT(TODAY(),"mmm/yy")

Previous Month and Year

=TEXT(EDATE(TODAY(),-1),"mmm/yy")

in VBA

CurrMth = Format(Date, "mmm/yy")
PrevMth = Format(WorksheetFunction.EDate(Date, -1), "mmm/yy")

Note again that WorksheetFunction.EDate does not work in VBA in some earlier
versions of XL.

--
Regards,

OssieMac

I am not sure if you want to work in VBA code or in worksheet formulas
because TODAY() is a worksheet formula and your CurrMth and PrevMth equals is
VBA.

In worksheet formulas the following returns the values you want.

Current month and year
= MONTH(TODAY()) & " " & YEAR(TODAY())

Previous month and year
=MONTH(EDATE(TODAY(),-1)) & " " & YEAR(EDATE(TODAY(),-1))

In VBA. [Note that Date replaces TODAY()]

CurrMth = Month(Date) & " " & Year(Date)

PrevMth = Month(WorksheetFunction.EDate(Date, -1)) _
& " " & Year(WorksheetFunction.EDate(Date, -1))

However, WorksheetFunction.EDate does not work in VBA in XL2002. Not sure
about xl2003 but it does work in xl2007.

--
Regards,

OssieMac


:

I would like to give a variable for my date column, can some one advise me

Example:
I have column in this formate mmm/yy, my below variable should identify the
Month & Year, i need to variables one is for current month(Jan/09), and the
other for Previous Month(Dec/08)

CurrMth = Month(Today()) & Year(Today())
PrvMth = Month(Today()) - 1 & Year(Today())

 

[Ranjit kurian]

Iam using Excel 2003.

What version of Excel are you using?

--
Regards,

OssieMac

Iam working in VBA.

Iam getting an error(object doesn't support) for the previous month Code.

Iam using an excel VBA, in excel i have a column called as MyDate which
contains only months like Jan/09, Dec/08, Nov/08,Oct/08 etc... i have
changed the format manually to mmm/yy format, but when i go to each cell its
still in the format of m/d/yyyy, because of which the vba code is giving an
error to me, as my date are not fixed, i need to change the formate of excel
into mmm/yy

Iam using the variables to an IF funtions, if the CurrMth is Jan/09 then the
result should be Yes, if the PrevMth is Dec-08 the result should be AYes

Some more answers that might be closer to what you want.

On worksheet
Current Month and Year

=TEXT(TODAY(),"mmm/yy")

Previous Month and Year

=TEXT(EDATE(TODAY(),-1),"mmm/yy")

in VBA

CurrMth = Format(Date, "mmm/yy")
PrevMth = Format(WorksheetFunction.EDate(Date, -1), "mmm/yy")

Note again that WorksheetFunction.EDate does not work in VBA in some earlier
versions of XL.

--
Regards,

OssieMac


:

I am not sure if you want to work in VBA code or in worksheet formulas
because TODAY() is a worksheet formula and your CurrMth and PrevMth equals is
VBA.

In worksheet formulas the following returns the values you want.

Current month and year
= MONTH(TODAY()) & " " & YEAR(TODAY())

Previous month and year
=MONTH(EDATE(TODAY(),-1)) & " " & YEAR(EDATE(TODAY(),-1))

In VBA. [Note that Date replaces TODAY()]

CurrMth = Month(Date) & " " & Year(Date)

PrevMth = Month(WorksheetFunction.EDate(Date, -1)) _
& " " & Year(WorksheetFunction.EDate(Date, -1))

However, WorksheetFunction.EDate does not work in VBA in XL2002. Not sure
about xl2003 but it does work in xl2007.

--
Regards,

OssieMac


:

I would like to give a variable for my date column, can some one advise me

Example:
I have column in this formate mmm/yy, my below variable should identify the
Month & Year, i need to variables one is for current month(Jan/09), and the
other for Previous Month(Dec/08)

CurrMth = Month(Today()) & Year(Today())
PrvMth = Month(Today()) - 1 & Year(Today())

 

[Dave Peterson]

In code:

Dim CurMth As String
Dim PrvMth As String

CurMth = Format(Date, "mmyyyy")
PrvMth = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmyyyy")

MsgBox CurMth & vbLf & PrvMth



I like 2 digit months. You may not want that.

 

[Ranjit kurian]

Thanks its working fine, but i have a doubt on PrvMth

Suppose if my CurMth is Feb/09, will the PrvMth give the result as Jan/09

 

[Harald Staff]

A date contains a day, a month, a year, and a time of day (midnight unless
specified). So a date can not contain a full month. You can format the
display (a worksheet cell, a messagebox, ...) to hide the day and time but
it is still there. You can in fact format the date as mostly anything, it
will not change the real date content.

Likewise, "Jan/09" is not a date because it has no day. It will never equal
a date variable, it might equal a string containing a formatted date
display, but if so you would depend too much on regional settings for my
european taste.

You must either work with real dates and compare its month and year
components, or convert the date to a string to compare it with another
sting. I would prefer to work with real dates, as you can do real math with
them. Strings are prety stupid, and depending on -oh, I wrote that already.

I can't find your code samples, so I can't provide working suggestions.

HTH. Best wishes Harald

Iam working in VBA.

Iam getting an error(object doesn't support) for the previous month Code.

Iam using an excel VBA, in excel i have a column called as MyDate which
contains only months like Jan/09, Dec/08, Nov/08,Oct/08 etc... i have
changed the format manually to mmm/yy format, but when i go to each cell
its
still in the format of m/d/yyyy, because of which the vba code is giving
an
error to me, as my date are not fixed, i need to change the formate of
excel
into mmm/yy

Iam using the variables to an IF funtions, if the CurrMth is Jan/09 then
the
result should be Yes, if the PrevMth is Dec-08 the result should be AYes

Some more answers that might be closer to what you want.

On worksheet
Current Month and Year

=TEXT(TODAY(),"mmm/yy")

Previous Month and Year

=TEXT(EDATE(TODAY(),-1),"mmm/yy")

in VBA

CurrMth = Format(Date, "mmm/yy")
PrevMth = Format(WorksheetFunction.EDate(Date, -1), "mmm/yy")

Note again that WorksheetFunction.EDate does not work in VBA in some
earlier
versions of XL.

--
Regards,

OssieMac

I am not sure if you want to work in VBA code or in worksheet formulas
because TODAY() is a worksheet formula and your CurrMth and PrevMth
equals is
VBA.

In worksheet formulas the following returns the values you want.

Current month and year
= MONTH(TODAY()) & " " & YEAR(TODAY())

Previous month and year
=MONTH(EDATE(TODAY(),-1)) & " " & YEAR(EDATE(TODAY(),-1))

In VBA. [Note that Date replaces TODAY()]

CurrMth = Month(Date) & " " & Year(Date)

PrevMth = Month(WorksheetFunction.EDate(Date, -1)) _
& " " & Year(WorksheetFunction.EDate(Date, -1))

However, WorksheetFunction.EDate does not work in VBA in XL2002. Not
sure
about xl2003 but it does work in xl2007.

--
Regards,

OssieMac


:

I would like to give a variable for my date column, can some one
advise me

Example:
I have column in this formate mmm/yy, my below variable should
identify the
Month & Year, i need to variables one is for current month(Jan/09),
and the
other for Previous Month(Dec/08)

CurrMth = Month(Today()) & Year(Today())
PrvMth = Month(Today()) - 1 & Year(Today())

 

[Dave Peterson]

I'm gonna say yes.

Dim CurMth As String
Dim PrvMth As String
Dim myDate As Date

myDate = DateSerial(2009, 2, 1) 'Use Date for today's date.

CurMth = Format(myDate, "mmyyyy")
PrvMth = Format(DateSerial(Year(myDate), Month(myDate) - 1, 1), "mmyyyy")

MsgBox CurMth & vbLf & PrvMth

=========
But read the response from Harald Staff. I'm not sure how the data in your
worksheet gets used in the code.