Formula is being quirky

[Pierre]

Formula trouble:


I have this formula in cell E15: =("LABOR MIDPOINT:
"&CHOOSE(MONTH(D14),"1ST","1ST","1ST","2ND","2ND","2ND","3RD","3RD","3RD","4TH","4TH","4TH")&"
QTR "&(YEAR(D14)+(MONTH(D14)>9)))


When I enter a date into D14, the formula result is usually the value
of D14's quarter, expressed as Labor Midpoint, the number of the
quarter, and the year.

However, when I enter 10/31/2008 into D14, it jumps to 3rd qtr, but
the "next" year, and not the current year of the value in D14.
10/31/2008 erroniously becomes "Labor Midpoint: 3rd Qtr 2009.

Any help troubleshooting would be appreciated.

Pierre

 

[Pete_UK]

Just get rid of the final term, i.e.:

=("LABOR MIDPOINT: "&CHOOSE(MONTH(D14),"1ST ","1ST ","1ST ","2ND
","2ND ","2ND ","3RD ","3RD ","3RD ","4TH ","4TH ","4TH ")&"QTR
"&(YEAR(D14)))

Hope this helps.

Pete

 

[John Bundy]

Cut the month stuff off the end, that will fix ya

=("LABOR
MIDPOINT:"&CHOOSE(MONTH(D14),"1ST","1ST","1ST","2ND","2ND","2ND","3RD","3RD","3RD","4TH","4TH","4TH")&"QTR"&(YEAR(D14)))

 

[bj]

a couple of issues.
10/31/08 should be fourth quarter
the 2009 comes from the month()>9 portion
this is probably fiscal year starting Oct 1.
If it is the quarter designators may be the portion in error.
Oct through Dec might need to be Q1 rather than Jan thru Mar.
I think someone mistakingly combined calendar Year and fiscal yearinto one
equation

which do you need it to be?

 

[Pierre]

Cut the month stuff off the end, that will fix ya

=("LABOR
MIDPOINT:"&CHOOSE(MONTH(D14),"1ST","1ST","1ST","2ND","2ND","2ND","3RD","3RD­","3RD","4TH","4TH","4TH")&"QTR"&(YEAR(D14)))
--
-John
Please rate when your question is answered to help us and others know what
is helpful.










- Show quoted text -

Thanks to both of you for your answers. One question: How do i get rid
of the little square box that appears after the word "Qtr", and the
year?

Thanks again.

Pierre

 

[Pierre]

a couple of issues.
10/31/08 should be fourth quarter
the 2009 comes from the month()>9 portion
this is probably fiscal year starting Oct 1.
If it is the quarter designators may be the portion in error.
Oct through Dec might need to be Q1 rather than Jan thru Mar.
I think someone mistakingly combined calendar Year and fiscal yearinto one
equation

which do you need it to be?

bj:

Calendar year, and the other suggestions seem to work, albiet for a
little square box that appears within the answer.

Pierre

 

[T. Valko]

Are these calendar quarters? If so, you can shorten that formula to:

="LABOR MIDPOINT: "&IF(D14="","",CHOOSE(INT((MONTH(D14)+2)/3),
"1ST","2ND","3RD","4TH")&" QTR "&YEAR(D14))

 

[Pierre]

Are these calendar quarters? If so, you can shorten that formula to:

="LABOR MIDPOINT: "&IF(D14="","",CHOOSE(INT((MONTH(D14)+2)/3),
"1ST","2ND","3RD","4TH")&" QTR "&YEAR(D14))

--
Biff
Microsoft Excel MVP











- Show quoted text -

Works real well now, thanks to all those who posted.

Pierre

 

[Sandy Mann]

Thanks to both of you for your answers. One question: How do i get rid

of the little square box that appears after the word "Qtr", and the
year?

I assume that they are *non-printing* line break characters, Char(10).

Position the cursor in the formula bar where the line wraps and hit the
Delete button to remove each one.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Cut the month stuff off the end, that will fix ya

=("LABOR
MIDPOINT:"&CHOOSE(MONTH(D14),"1ST","1ST","1ST","2ND","2ND","2ND","3RD","3RD­","3RD","4TH","4TH","4TH")&"QTR"&(YEAR(D14)))
--
-John
Please rate when your question is answered to help us and others know what
is helpful.










- Show quoted text -

Thanks to both of you for your answers. One question: How do i get rid
of the little square box that appears after the word "Qtr", and the
year?

Thanks again.

Pierre

 

[bj]

where does the little square box show?

bj:

Calendar year, and the other suggestions seem to work, albiet for a
little square box that appears within the answer.

Pierre

 

[T. Valko]

Are these calendar quarters? If so, you can shorten that formula to:

="LABOR MIDPOINT: "&IF(D14="","",CHOOSE(INT((MONTH(D14)+2)/3),
"1ST","2ND","3RD","4TH")&" QTR "&YEAR(D14))

--
Biff
Microsoft Excel MVP











- Show quoted text -

Works real well now, thanks to all those who posted.

Pierre

You're welcome. Thanks for the feedback!