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
-