Function for returning quarter based on current date?

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!

Originally Posted by 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 :
> 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
>
>
>
>

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!

Originally Posted by 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 :
> 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
>>>
>>>
>>>
>>> -
>
>
>
>