Formula

[ceblaser]

I have inserted a formula into a worksheet that identifies the first
month of the following quarter. For example,

DATE 1st Month of Next Quarter (per formula)

01/19/94 April
07/01/99 October
04/28/93 July
05/02/93 July
03/16/97 April
10/22/96 January



However, I need to have the appropriate year listed next to the name of
the month. So this is how I need it to look . . .

DATE 1st Month of Next Quarter (per formula)

01/19/94 April 1994
07/01/99 October 1999
04/28/93 July 1993
05/02/93 July 1993
03/16/97 April 1997
10/22/96 January 1997

However, am having a hard time creating the correct formula to
accomplish this. Any help you can offer would be greatly appreciated.

Thanks!!!!

 

[davers]

Lets assume for your example you have in cell A1 01/19/94. Is that what
you mean? And if so, then your formula in cell B1 must be the one that
returns April? Could you post that? I could use it... If you
mean how do you put just the year of cell B1 in cell C1, the formula is
=TEXT(B1,"yyyy").

Sorry if I've misunderstood what you are requesting....

Dave M

 

[Peo Sjoblom]

One way

=TEXT(DATE(YEAR(A2),INDEX({4;7;10;13},MATCH(MONTH(A2),{1;4;7;10})),1),"mmmm
yyyy")

where the date starts in A2, copy down as long as needed

 

[James S]

Try adding this to your existing formula:

(ABC = Date)
(XYZ = Your Formula)

=XYZ & " " & IF(OR(MONTH(ABC)=10,MONTH(ABC)=11,MONTH(ABC)
=12),YEAR(ABC)+1,YEAR(ABC))

There may be a shorter way to write a similar formula, but
at least this will work in the mean time.

Hope it helps.

Regards,
James S

-----Original Message-----

I have inserted a formula into a worksheet that identifies the first
month of the following quarter. For example,

DATE 1st Month of Next Quarter (per formula)

01/19/94 April
07/01/99 October
04/28/93 July
05/02/93 July
03/16/97 April
10/22/96 January



However, I need to have the appropriate year listed next to the name of
the month. So this is how I need it to look . . .

DATE 1st Month of Next Quarter (per formula)

01/19/94 April 1994
07/01/99 October 1999
04/28/93 July 1993
05/02/93 July 1993
03/16/97 April 1997
10/22/96 January 1997

However, am having a hard time creating the correct formula to
accomplish this. Any help you can offer would be greatly appreciated.

Thanks!!!!

http://www.ExcelForum.com/

 

[James S]

Just in case I made it confusing, here's an example:

Date's Your Formula New Formula
Here Returns Returns
A B C
1 01/19/94 April April 1994
2 10/22/96 January January 1997

New formula in Cell C1 is:
(XYZ = Your Formula)

=XYZ & " " & IF(OR(MONTH(A1)=10,MONTH(A1)=11,MONTH(A1)
=12),YEAR(A1)+1,YEAR(A1))

Hope that makes it clearer.

Regards,
James S