Month: Word to Number

[Sarah K]

Is there a formula in Excel VB that will allow me to convert a month's name to its numberic equivalent?

So for example:
October to 10
June to 6 etc...


I know I can do a lookup or something, but it there anything slicker?
Any help would be great

 

[monirg]

What if I need the "reverse" in a w/s cell ??
That's to say: month Number to Word.

For example:
9 to September
12 to December
etc.

Let us say:
in D12 I have: 30/09/2011
in D13 I have: Month(D12) which returns 9
and I want it to return September instead.
I tried to reverse your formula, but didn't work!

Regards.

 

[Jonmo1]

What if I need the "reverse" in a w/s cell ??
That's to say: month Number to Word.

For example:
9 to September
12 to December
etc.

Let us say:
in D12 I have: 30/09/2011
in D13 I have: Month(D12) which returns 9
and I want it to return September instead.
I tried to reverse your formula, but didn't work!

Regards.

If you already have the actual date in D12, use

=TEXT(D12,"mmmm")

 

[monirg]

jonmo1;

Perfect! Thank you very much.

Regards.

 

[T. Valko]

What if I need the "reverse" in a w/s cell ??
That's to say: month Number to Word.

For example:
9 to September
12 to December
etc.

Let us say:
in D12 I have: 30/09/2011
in D13 I have: Month(D12) which returns 9
and I want it to return September instead.
I tried to reverse your formula, but didn't work!

Regards.

To get the month name from the month number...

Book1
	A	B
1	1	January
2	2	February
3	3	March
4	4	April
5	5	May
6	6	June
7	7	July
8	8	August
9	9	September
10	10	October
11	11	November
12	12	December
Sheet1

=TEXT(A1*29,"mmmm")

 

[monirg]

Biff;

Let us assume I don't have the actual date in D12
And let us assume I only have the month number returned in D13 as 9

To use your formula, I'd fill in the number/name month table in F21:G32 as per your reply
What is the formula I should enter in D20 to return the month name based on the month number of D13 and the table F21:G32 ??
{Not sure sure how to use TEXT() here as you suggested instead VLOOKUP()}

Regards.

 

[T. Valko]

Biff;

Let us assume I don't have the actual date in D12
And let us assume I only have the month number returned in D13 as 9

To use your formula, I'd fill in the number/name month table in F21:G32 as per your reply
What is the formula I should enter in D20 to return the month name based on the month number of D13 and the table F21:G32 ??
{Not sure sure how to use TEXT() here as you suggested instead VLOOKUP()}

Regards.

I may not have been clear in my other post.

I should have said:

Enter this formula in B1 and copy down to B12:

=TEXT(A1*29,"mmmm")

You don't need a lookup table to do this. The general formula is:

=TEXT(month_number*29,"mmmm")

To answer your question about using a table...

D13 = the month number, a number from 1 to 12

The lookup table in the range F21:G32

One of these:

=INDEX(G21:G32,D13)
=VLOOKUP(D13,F21:G32,2,0)

Of those, the INDEX version would be my choice.

 

[monirg]

Biff;

Thank you for the clarification.

Let us assume I don't have the actual date in D12
(otherwise, jonmo1's suggested formula would apply: =TEXT(D12,"mmmm")

And let us assume I only have the month number returned in D13 as 9

In this case, your suggested formula applies: =TEXT(D13*29,"mmmm")

There's no need really to create the month number/name table!
That was the confusing part.

Thanks again for your help.

 

[T. Valko]

Biff;

Thank you for the clarification.
In this case, your suggested formula applies: =TEXT(D13*29,"mmmm")

There's no need really to create the month number/name table!
That was the confusing part.

Thanks again for your help.

You're welcome!