I am in need of a function that will return a quarter based upon the existing date. For example, if todays current date is 3/31/12 and I am working with a future date of 3/31/16 I need a function that would return quarter 16 (being 16 quarters from my "current date"), and further any date between 1/1/18 and 3/31/18 would return a quarter 24 (being 24 quarters form my "current date"). I need this to be accurate through 40 quarters. Please help.
As a futher example:
Current date=3/31/12
Dates 4/1/12-6/30/12=Quarter 1
Dates 7/1/12-9/30/12=Quarter 2
Dates 10/1/12-12/31/12=Quarter 3
Dates 1/1/13-3/31/13=Quarter 4
Dates 4/30/13-6/30/13=Quarter 5
Dates 7/1/13-9/30/13=Quarter 6

and so on

It needs to work that so anytime I change the current date the quarters follow suit.

Thank you

hi,

supposing that cell A1 contains the date (03/31/12)

cell A2 contains the formula:
=DATE(YEAR(A1),MONTH(A1)+1,1)

cell B2 contains the formula:
=DATE(YEAR(A2),MONTH(A2)+3,1)-1

cell A3 contains the formula:
=B2+1

cell B3 contains the formula:
=DATE(YEAR(A3),MONTH(A3)+3,1)-1

and now copy cell "A3:B3" down


--
isabelle





Le 2012-06-20 17:01, Steele1978 a écrit :
I am in need of a function that will return a quarter based upon the
existing date. For example, if todays current date is 3/31/12 and I am
working with a future date of 3/31/16 I need a function that would
return quarter 16 (being 16 quarters from my "current date"), and
further any date between 1/1/18 and 3/31/18 would return a quarter 24
(being 24 quarters form my "current date"). I need this to be accurate
through 40 quarters. Please help.
As a futher example:
Current date=3/31/12
Dates 4/1/12-6/30/12=Quarter 1
Dates 7/1/12-9/30/12=Quarter 2
Dates 10/1/12-12/31/12=Quarter 3
Dates 1/1/13-3/31/13=Quarter 4
Dates 4/30/13-6/30/13=Quarter 5
Dates 7/1/13-9/30/13=Quarter 6

and so on

It needs to work that so anytime I change the current date the quarters
follow suit.

Thank you

Thanks Isabelle but that is not quite what I am looking for. But I can see a use for that in something else I am working with. It is hard to put into words what it is exactly I am looking for but I will try again. I need a function that will return the quarter # that coincides to those date ranges. So if my current date is 3/31/12 and I have detail with a maturity date in the future of something like 4/17/2016 the function would return a value of 17 (4/17/16 being 17 quarters from 3/31/12, anything from 4/1/16 through 6/30/16 would bring back a value of 17 in this case) Any other ideas?

Thanks for your help either way!

ok i understand better now,
i see two possibilities

=(DATEDIF(A1,B1,"m")/3)+1

http://www.cpearson.com/excel/datedif.aspx


or

=CEILING(SUMPRODUCT(1*(DAY(ROW(INDIRECT(DATE(YEAR( $A1),MONTH($A1),DAY(A1)+1)&":"&DATE(YEAR(B1),MONTH (B1),DAY(B1)))))=1))/3,1)


--
isabelle



Le 2012-06-21 18:57, Steele1978 a écrit :
Thanks Isabelle but that is not quite what I am looking for. But I can
see a use for that in something else I am working with. It is hard to
put into words what it is exactly I am looking for but I will try again.
I need a function that will return the quarter # that coincides to
those date ranges. So if my current date is 3/31/12 and I have detail
with a maturity date in the future of something like 4/17/2016 the
function would return a value of 17 (4/17/16 being 17 quarters from
3/31/12, anything from 4/1/16 through 6/30/16 would bring back a value
of 17 in this case) Any other ideas?

Thanks for your help either way!

isabelle;1602947 Wrote:
hi,

supposing that cell A1 contains the date (03/31/12)

cell A2 contains the formula:
=DATE(YEAR(A1),MONTH(A1)+1,1)

cell B2 contains the formula:
=DATE(YEAR(A2),MONTH(A2)+3,1)-1

cell A3 contains the formula:
=B2+1

cell B3 contains the formula:
=DATE(YEAR(A3),MONTH(A3)+3,1)-1

and now copy cell "A3:B3" down


--
isabelle





Le 2012-06-20 17:01, Steele1978 a écrit :-
I am in need of a function that will return a quarter based upon the
existing date. For example, if todays current date is 3/31/12 and I
am
working with a future date of 3/31/16 I need a function that would
return quarter 16 (being 16 quarters from my "current date"), and
further any date between 1/1/18 and 3/31/18 would return a quarter 24
(being 24 quarters form my "current date"). I need this to be
accurate
through 40 quarters. Please help.
As a futher example:
Current date=3/31/12
Dates 4/1/12-6/30/12=Quarter 1
Dates 7/1/12-9/30/12=Quarter 2
Dates 10/1/12-12/31/12=Quarter 3
Dates 1/1/13-3/31/13=Quarter 4
Dates 4/30/13-6/30/13=Quarter 5
Dates 7/1/13-9/30/13=Quarter 6

and so on

It needs to work that so anytime I change the current date the
quarters
follow suit.

Thank you



-

Thank you, thank you, thank you Isabelle. That second one is exactly what I needed. You my friend are a genius. I have been trying to get an answer to that one for a long time now and you are the first person to supply one for me!