If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Function for returning quarter based on current date?
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Function for returning quarter based on current date?

#1
June 20th 12, 10:01 PM
 Steele1978 Junior Member First recorded activity by ExcelBanter: Jun 2012 Posts: 4
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
#2
June 21st 12, 04:27 AM posted to microsoft.public.excel.worksheet.functions
 isabelle external usenet poster Posts: 449
Function for returning quarter based on current date?

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
> 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
>
> Thank you
>
>
>
>

#3
June 21st 12, 11:57 PM
 Steele1978 Junior Member First recorded activity by ExcelBanter: Jun 2012 Posts: 4

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!

Quote:
 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 > > > >
#4
June 22nd 12, 02:00 PM posted to microsoft.public.excel.worksheet.functions
 isabelle external usenet poster Posts: 449
Function for returning quarter based on current date?

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
>>> 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
>>>
>>> Thank you
>>>
>>>
>>>
>>> -

>
>
>
>

#5
June 26th 12, 07:18 PM
 Steele1978 Junior Member First recorded activity by ExcelBanter: Jun 2012 Posts: 4

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!

Quote:
 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 >>> >>> >>> >>> - > > > >

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Quarter function based on current date Steele1978 Excel Worksheet Functions 1 June 8th 12 12:00 AM If Statement - Returning Quarter based on Specific Date Jamie New Users to Excel 4 March 30th 10 04:38 PM Open file with variable date name based on current date Ciprian Excel Programming 1 January 7th 10 02:31 PM Return the current Yearly Quarter based on date hozman Excel Worksheet Functions 2 November 12th 09 11:29 PM formula IF returning current date diane Excel Worksheet Functions 1 March 10th 05 06:54 AM

All times are GMT +1. The time now is 09:58 AM.