Month formula always returns Jan

  • Thread starter Thread starter brianwa
  • Start date Start date
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
 
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)
 
U know what this formula is doing right? it goes down as many rows from
o20:o31 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:o31 contain text strings
like "Jan"?

You say you want to return a month value. In that case, with this formula,
the contents of o20:o31 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...
 
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
 
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
 
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...
 
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...
 
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...
 
Anders,

mmm and MMM both return a month in proper case, e.g. Nov.

mmm returns a full month name, e.g. November.

Minutes are returned with mm.

--

HTH

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

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...
 
Bob,

You don't have to reply to this, but rereading your message
mmm and MMM both return a month in proper case, e.g. Nov.
mmm returns a full month name, e.g. November.
Click to expand...

Anders
P.S. My spellchecker also wants to change mmm to mom.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date Lookup 8
Fiscal Month Formula 3
dates of month automation 3
Formula to calculate a sum 1
Match?,Index?, Sum? or some other way? 4
Index(Match) question 4
Looking formula that gives total of remaining months 6
count total dates for each month 2

Back
Top