Formula is being quirky

  • Thread starter Thread starter Pierre
  • Start date Start date
P

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
 
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
 
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)))
 
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?
 
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 -
Click to expand...

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
 
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?
Click to expand...

bj:

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

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))
 
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 -
Click to expand...


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

Pierre
 
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?
Click to expand...

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 -
Click to expand...

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
 
where does the little square box show?

Pierre said:
bj:

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

Pierre
Click to expand...
 
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 -
Click to expand...


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

Pierre

You're welcome. Thanks for the feedback!
 
Back
Top