Month text as a value

[DaveKid]

Hi, I am wokring on a spreadsheet which gives the month name in a drop down
list to select. This drop down box is then linked to another cell which reads
is contents to give the value dependent on which month is chosen, however, I
have a problem in that excel cannot read the month as a value. How do I
overcome this?

 

[T. Valko]

If you select October from the drop down list what value should that equate
to?

 

[Bernard Liengme]

Not sure I understand problem but does this help
=LOOKUP(A1,{"Jan","Feb","Mar","Apr"},{1,2,3,4})
If A1 has value Feb this returns number 2, etc
I got tired of typing so add more months
best wishes

 

[Ron Rosenfeld]

Hi, I am wokring on a spreadsheet which gives the month name in a drop down
list to select. This drop down box is then linked to another cell which reads
is contents to give the value dependent on which month is chosen, however, I
have a problem in that excel cannot read the month as a value. How do I
overcome this?

I assume your month names are in a list someplace, in order, so:

=MATCH(A1,List_of_Months,0)

will return the number of the month.
--ron

 

[Harald Staff]

Another:
=MONTH(A1&" 1. 2008")

Best wishes Harald

 

[Dave Peterson]

The dot didn't work for me (my USA settings???).

But this did:
=MONTH(A1&" 1, 2008")

As did this:
=MONTH("1"&A1&"2008")
and
=MONTH(1&A1&2008)
(and I didn't have to worry about the separator)

 

[Harald Staff]

Of course, faults are all in your settings Dave, solution is perfect <bg>.
No, here (northern Europe) it is
=MONTH("1. "&A1&" 2008")
and I felt pretty sure that just switching positions would do the trick.
Thank you for the correction.

Dates in Excel are strange. Here in Norway you an type almost any kind of
date and Excel gets it. The Danish version (neighbor country with pretty
much the same language) is extremely picky and I think you must be a
scientist to enter a date into a cell properly.

Best wishes Harald

 

[Dave Peterson]

The fault, dear Harald, is not in our settings, but in ourselves...

(just in case...)
http://www.enotes.com/shakespeare-quotes/fault-dear-brutus-our-stars

 

[DaveKid]

Well, this should be number 11 as the fiscal year starts from November.

 

[DaveKid]

No. That doesnt work.

Not sure I understand problem but does this help
=LOOKUP(A1,{"Jan","Feb","Mar","Apr"},{1,2,3,4})
If A1 has value Feb this returns number 2, etc
I got tired of typing so add more months
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

 

[DaveKid]

No this doesnt wortk either.

I assume your month names are in a list someplace, in order, so:

=MATCH(A1,List_of_Months,0)

will return the number of the month.
--ron

 

[DaveKid]

Basically..... I have 10,000 columns of data each defined by the month they
are in with the month name November, December etc. I have a list of the
months in a drop down menu which when selected will give the amount of data
for that month. I am trying to give a year to date value which will calculate
the month and the months prior to the month selected but I can not do this.

 

[Ron Rosenfeld]

No this doesnt wortk either.

Then what you posted does not match what you really have.
--ron

 

[DaveKid]

Hi Ron

You were not specific in where I put this. In the cell with the month in? or
next to it. I need the value to be in my list of months. What you have given
me does not work and I dont think you understand the problem.

 

[Harald Staff]

Beautiful!

(You're not a real geek after all, confess

Best wishes Harald

 

[Ron Rosenfeld]

Hi Ron

You were not specific in where I put this. In the cell with the month in? or
next to it. I need the value to be in my list of months. What you have given
me does not work and I dont think you understand the problem.

You are correct that I do not understand the problem.

The formula can be put in any cell other than a precedent cell.

It should return the month number. What did it return?
--ron

 

[Dave Peterson]

A longggggg time ago, I was a Math major with an English minor.

But that was because I liked the grammar/syntax stuff (not the literature
side!).

Beautiful!

(You're not a real geek after all, confess

Best wishes Harald