Month formula always returns Jan

B

brianwa

I'm trying to return a month value using the formula below
=INDEX($O$20:$O$31,MONTH(C20))
Where I pass months Jan-Dec throught the index formula.
The problem is that I'm always getting Jan as a result regardless of
the month number.

Thanks in advance
BW
 
B

Bob Phillips

Brian,

Why not just try

=TEXT(C20,"mom")


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
D

Dave R.

U know what this formula is doing right? it goes down as many rows from
o20:eek:31 as is the month value in C20 -- and returns the value of the cell
there. If C20 is a january date (1), it returns to you the value of o20. if
C20 is a feb date (2) it returns the value of o21.

If it returns "Jan" to you, does it mean that o20:eek:31 contain text strings
like "Jan"?

You say you want to return a month value. In that case, with this formula,
the contents of o20:eek:31 would have to be dates, and the formula would need a
MONTH( ) around it. I don't see how that would be useful since you know the
month number of the month being looked up by the formula (i.e. the same).

Hope this sheds light...
 
A

Anders S

BW,

The reason you get Jan as the answer to MONTH(x) where x is 1-12 is that the numbers 1-12 in date format are the first twelve days in Excel's date system i.e. Jan 1-12 1900.

Better use Bob' suggestion, (but it's got to be) =TEXT(C20,"MMM").

HTH
Anders Silven
 
B

Bob Phillips

Anders,

No,

=TEXT(c20,"mmm")

works fine. it doesn't need MMM (at least not on my machine).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

BW,

The reason you get Jan as the answer to MONTH(x) where x is 1-12 is that the
numbers 1-12 in date format are the first twelve days in Excel's date system
i.e. Jan 1-12 1900.

Better use Bob' suggestion, (but it's got to be) =TEXT(C20,"MMM").

HTH
Anders Silven
 
A

Anders S

Bob,

On my Swedish system "m" represents minutes and "M" represents months in formatting.
What do you use for minutes and what does "MMM" return?

Confused

Anders Silven

Bob Phillips said:
Anders,

No,

=TEXT(c20,"mmm")

works fine. it doesn't need MMM (at least not on my machine).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

BW,

The reason you get Jan as the answer to MONTH(x) where x is 1-12 is that the
numbers 1-12 in date format are the first twelve days in Excel's date system
i.e. Jan 1-12 1900.

Better use Bob' suggestion, (but it's got to be) =TEXT(C20,"MMM").

HTH
Anders Silven
Click to expand...
 
P

Peo Sjoblom

Anders,

mmm and MMM returns the same value on US regional settings


--

Regards,

Peo Sjoblom

Bob,

On my Swedish system "m" represents minutes and "M" represents months in
formatting.
What do you use for minutes and what does "MMM" return?

Confused

Anders Silven

Bob Phillips said:
Anders,

No,

=TEXT(c20,"mmm")

works fine. it doesn't need MMM (at least not on my machine).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

BW,

The reason you get Jan as the answer to MONTH(x) where x is 1-12 is that the
numbers 1-12 in date format are the first twelve days in Excel's date system
i.e. Jan 1-12 1900.

Better use Bob' suggestion, (but it's got to be) =TEXT(C20,"MMM").

HTH
Anders Silven
Click to expand...
 
A

Anders S

Thank you for the clarification, Peo,

With Swedish settings "m", "mm", "mmm" and even "mmmmmmmm" return minutes:

Hope I didn't confuse anybody.

Best regards
Anders Silven

Peo Sjoblom said:
Anders,

mmm and MMM returns the same value on US regional settings


--

Regards,

Peo Sjoblom

Bob,

On my Swedish system "m" represents minutes and "M" represents months in
formatting.
What do you use for minutes and what does "MMM" return?

Confused

Anders Silven
Click to expand...
 
Top