Function for returning quarter based on current date?

[Steele1978]

I am in need of a function that will return a quarter based upon th
existing date. For example, if todays current date is 3/31/12 and I a
working with a future date of 3/31/16 I need a function that woul
return quarter 16 (being 16 quarters from my "current date"), an
further any date between 1/1/18 and 3/31/18 would return a quarter 2
(being 24 quarters form my "current date"). I need this to be accurat
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 quarter
follow suit.

Thank yo

 

[isabelle]

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 :

 

[Steele1978]

Thanks Isabelle but that is not quite what I am looking for. But I ca
see a use for that in something else I am working with. It is hard t
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 t
those date ranges. So if my current date is 3/31/12 and I have detai
with a maturity date in the future of something like 4/17/2016 th
function would return a value of 17 (4/17/16 being 17 quarters fro
3/31/12, anything from 4/1/16 through 6/30/16 would bring back a valu
of 17 in this case) Any other ideas?

Thanks for your help either way!

 

[isabelle]

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 :

 

[Steele1978]

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