Excel number of month 1

[swtrader]

I need to return the 'month-number' in a cell. -- i.e., 7 for July (not 07, which is what I get on anything that I try).

Suggestions?

Thanks.

swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[PHV]

It's just a number format issue, isn't it ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GoDawgs]

Just format as "m", not "mm".

 

[SkipVought]

Hi,

How about

dim iMonth as integer, sMonth as string
iMonth = Month(YourDate)
sMonth = Format(Yourdate, "m")

If you want the Month NUMBER, do not use the FORMAT function which returns a STRING (which, of course, you could COERCE to a numeric value)


Skip,

_[red][/red]

 

[swtrader]

I messed up the request. It's the year that I need in single digit form. I've tried "y" but I still get 07. A department has made the single digit a portion of a tracking number. (I don't question -- I just try to serve.)

Thanks.

Oh, please be detailed as to where and how I use the formatting. I'm a VBA newbie.

swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[SkipVought]

"It's the year that I need in single digit form"


WHAT? The YEAR is 2007

What you want is the last two digits of the year in one digit format.

iYear = Cint(Format(TheDate, "yy"))

Skip,

_[red][/red]

 

[PHV]

Right(Year(theDate), 1)
or
Year(theDate) Mod 10

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[swtrader]

PHV -- Right worked (I had tried that but didn't include "Year") Thanks.

Skip -- I put =Cint(Format(AE3,"yy")) in cell AE4 and got #NAME? . for future ref, what did I do wrong? Thanks.

swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[SkipVought]

This is a VBA forum. I gave you VBA code, not SHEET code.

Skip,

_[red][/red]

 

[SkipVought]

Right(Year(theDate), 1) will not give you the results you want for years greater than 2009 or less than 2000.

Skip,

_[red][/red]