Quarter End Date

What is a formula that can be used to find the nearest quarter end,
when the fiscal year has a non-standard start date (ie. November fiscal
year, current date is 2/28/2005, quarter end should be 4/30/05)?

=DATE(YEAR(A1),(INT((MONTH(A1)+1)/3)+1)*3-1,0)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

<barry.s.le.vine@gmail.com> wrote in message
news:1144325705.584559.29330@j33g2000cwa.googlegroups.com...
> What is a formula that can be used to find the nearest quarter end,
> when the fiscal year has a non-standard start date (ie. November fiscal
> year, current date is 2/28/2005, quarter end should be 4/30/05)?
>

Thanks very much--this is perfeect. Is there a way to generalize for
any given fiscal year start (ie. use 11 for November as an input)?

=DATE(YEAR(A1),(INT((MONTH(A1)-1)/3)+1)*3+CHOOSE(MOD(MONTH(fiscal_start_date
),3)+1,0,1,2),0)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

<barry.s.le.vine@gmail.com> wrote in message
news:1144335827.767368.141820@z34g2000cwc.googlegroups.com...
> Thanks very much--this is perfeect. Is there a way to generalize for
> any given fiscal year start (ie. use 11 for November as an input)?
>

Is there a way to generalize for any fiscal month start using the
following formula:

DATE(YEAR(A1),CEILING(MONTH(A1),3)+1,DAY(0))

This I find to be best because it can be used to find half-year and
year-end intervals as well.

Do you mean you want the last date of the month for any date you put in A1,
or do you mean something else?

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



<barry.s.le.vine@gmail.com> wrote in message
news:1144339429.778402.172670@i40g2000cwc.googlegroups.com...
> Is there a way to generalize for any fiscal month start using the
> following formula:
>
> DATE(YEAR(A1),CEILING(MONTH(A1),3)+1,DAY(0))
>
> This I find to be best because it can be used to find half-year and
> year-end intervals as well.
>

Not really. Trying to write a generalizable formula to find the
nearest month, quarter, half-year, or year-end, but able to accomodate
different fiscal year start dates.

For example, if the Fiscal Year begins in November and today's date is
today, 4/6/06.

Nearest month end: 4/30/06
Nearest quarter end: 4/30/06
Nearest half-year end: 4/30/06
Nearest year-end: 10/31/06

If the fiscal year began in January, then it would be:

Nearest month end: 4/30/06
Nearest quarter end: 6/30/06
Nearest half-year end: 6/30/06
Nearest year-end: 12/31/06