Calculating Fiscal Quarter End Dates

[tgause]

Would anyone know a simple formula for doing this? My goal is to have a formula that will give me the fiscal quarter end dates for any given scenario. For example, if a company's fiscal year ends 12/31/2009, I would like a formula that would spit out 03/31/2010, 06/30/2010, and 09/30/2010 for the 1st, 2nd, and 3rd quarter end dates. I found the following formula online today. It get me close, but gives me all the exact dates. For example, it gives me 07/01/2010 instead of 06/30/2010.

=DATE(YEAR(A1),MONTH(A1)+3,DAY(A1))

Any help would be GREATLY appreciated.

tg

[teylyn]

try

=DATE(YEAR(A1),MONTH(A1)+4,0)

[tgause]

Almost. But I get 06/29/2010 instead of 06/30/2010; and 09/27/2010 instead of 09/30/2010. Any other suggestions?

[tgause]

p.s. This is another formula that almost gets me there, but not quite.

=DATE(YEAR(G10),CEILING(MONTH(G10),3)+1,0)

[teylyn]

Are you sure you entered the formula correctly?

starting with
31/12/2009

I get this:
31/03/2010 =DATE(YEAR(B1),MONTH(B1)+4,0)
30/06/2010 =DATE(YEAR(B2),MONTH(B2)+4,0)
30/09/2010 =DATE(YEAR(B3),MONTH(B3)+4,0)
31/12/2010 =DATE(YEAR(B4),MONTH(B4)+4,0)

etc.

see attached.

[tgause]

Many thanks!!! It works perfectly.

[tgause]

Follow up question: Is your formula supposed to adjust for different fiscal year ends, for example 12/29/2009? When I plug in this date, the fiscal quarter end dates do not change....