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


On Jul 23, 8:42 pm, Pierre wrote:
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

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

=("LABOR
MIDPOINT:"&CHOOSE(MONTH(D14),"1ST","1ST","1ST","2N D","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.


"Pierre" wrote:

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

On Jul 23, 3:10 pm, John Bundy (remove) wrote:
Cut the month stuff off the end, that will fix ya

=("LABOR
MIDPOINT:"&CHOOSE(MONTH(D14),"1ST","1ST","1ST","2N D","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.



"Pierre" wrote:
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- Hide quoted text -

- 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

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


Replace @mailinator.com with @tiscali.co.uk


"Pierre" wrote in message
ps.com...
On Jul 23, 3:10 pm, John Bundy (remove) wrote:
Cut the month stuff off the end, that will fix ya

=("LABOR
MIDPOINT:"&CHOOSE(MONTH(D14),"1ST","1ST","1ST","2N D","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.



"Pierre" wrote:
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- Hide quoted text -

- 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

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" wrote:

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

On Jul 23, 3:30 pm, bj wrote:
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

where does the little square box show?

"Pierre" wrote:

On Jul 23, 3:30 pm, bj wrote:
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

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


"Pierre" wrote in message
ups.com...
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

On Jul 23, 3:54 pm, "T. Valko" wrote:
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

"Pierre" wrote in message

ups.com...



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- Hide quoted text -

- Show quoted text -


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

Pierre

"Pierre" wrote in message
oups.com...
On Jul 23, 3:54 pm, "T. Valko" wrote:
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

"Pierre" wrote in message

ups.com...



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- Hide quoted text -

- Show quoted text -


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

Pierre

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP