month as number question

[xcellnoob]

I feel like a big dummy today.
I want to enter in cell C3 the month number. (Say 11 for November)
I want cell D3 to spit out "November"
For some reason no matter what number I put in C3, D3 always comes out as January???
Help?
BTW, formatting is all correct, so I don't think that's the issue.
Thanks for any help.

 

[barry houdini]

Here's one way......

=TEXT(DATE(,C3,1),"mmmm")

 

[Scott Huish]

Perhaps:

=TEXT(C3*29,"mmmm")

 

[xcellnoob]

Perhaps:

=TEXT(C3*29,"mmmm")

I think I understand the previous one(And it worked), but how does this one work? (It does)

 

[xcellnoob]

Also,
What if I have it the other way around where
C3 is entered as Jan-06.
I want D3 to return the number. (1)
I can do that easy enough, but now when I reference D3 in another formula it returns 37609 or something. How do I extract the number 1?
Thanks.

 

[Scott Huish]

Excel stores dates as serial numbers (In the 1900 date system (PC default) 1 is January 1, 1900 and so forth)
Multiplying the month by 29 makes the date in a month that applies to the number entered, see below example).

This also works in the 1904 date system.

We don't care about the year, because all we want is the month, so that doesn't matter:

Book3
	A	B	C	D	E
1	1	29	1/29/1900		January
2	2	58	2/27/1900		February
3	3	87	3/27/1900		March
4	4	116	4/25/1900		April
5	5	145	5/24/1900		May
6	6	174	6/22/1900		June
7	7	203	7/21/1900		July
8	8	232	8/19/1900		August
9	9	261	9/17/1900		September
10	10	290	10/16/1900		October
11	11	319	11/14/1900		November
12	12	348	12/13/1900		December
Sheet1

 

[barry houdini]

Also,
What if I have it the other way around where
C3 is entered as Jan-06.
I want D3 to return the number. (1)
I can do that easy enough, but now when I reference D3 in another formula it returns 37609 or something. How do I extract the number 1?
Thanks.

If C3 has a date then to extract the month number

=MONTH(C3)

is that what you mean?

 

[xcellnoob]

That doesn't seem to work.
It gives me 1 no matter what I enter.

 

[xcellnoob]

Excel stores dates as serial numbers (In the 1900 date system (PC default) 1 is January 1, 1900 and so forth)
Multiplying the month by 29 makes the date in a month that applies to the number entered, see below example).

This also works in the 1904 date system.

We don't care about the year, because all we want is the month, so that doesn't matter:

I see Senor.
Very nice. I love learning.

 

[Scott Huish]

=MONTH(C3) should work if C3 contains a date.

Book5
	C	D	E	F
3	9/16/2006	9
Sheet1