Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Function for returning quarter based on current date?
1,336 views
Skip to first unread message
Steele1978
Jun 20, 2012, 5:01:23 PM6/20/12
to
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
--
Steele1978
isabelle
Jun 20, 2012, 11:27:53 PM6/20/12
to
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
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
Steele1978
Jun 21, 2012, 6:57:38 PM6/21/12
to
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
>
>
>
>
>
> > 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
> >
> >
> >
> >-
> > 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
> >
> >
> >
--
Steele1978
isabelle
Jun 22, 2012, 9:00:03 AM6/22/12
to
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
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
Steele1978
Jun 26, 2012, 2:18:32 PM6/26/12
to
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!
isabelle;1603012 Wrote:
> 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
>
>
>
> > 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:-
> 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!
> >
> >> 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 :--
> >>
> >> 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
> >>
> >>
> >>
> >>
> >>
> >>> 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-
> the
> >>> existing date. For example, if todays current date is 3/31/12 and
> >> 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-
> >>> 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
> >> 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-
> >>> 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
> >>>
> >> quarters-
> >>> follow suit.
> >>>
> >>> Thank you
> >>>
> >>>
> >>>
> >>> ---
> >
> >
> >
> >-
--
Steele1978
0 new messages