Calculate nearest quarter end with date and month 4 financial year end as input

[Syerram]

Hi,


I am trying to calculate the quarter end date for a given date, with month for financial year end as input.


Ex:

Input date - 21/05/2013 (Cell A1)

Input Month for financial year end - 8 (august) (Cell B1)

Result should be: 31/05/2013


I tried using this formula - EOMONTH( DATE( YEAR( A1 ), ( INT( ( MONTH( A1 ) - 1 ) / 3 ) + 1 ) * 3 + CHOOSE( MOD( B1,3 ) + 1, 0, 1, 2), 1 ), 0)


It doesnt seem to be working for few months (it works for some months and dates)


Is there a neater and quicker way of doing it please?


Thank you!


Yerram

 

[SirJB7]

Hi, Syerram!

Tried with this?

=FECHA(AÑO(A1);ENTERO((MES(A1)+2)/3)*3+1;0) -----> in english: =DATE(YEAR(A1),INT((MONTH(A1)+2)/3)*3+1,0)

It worked for me for the whole 2012.

Regards!

PS: I didn't understand that thing of the financial year, could you elaborate?

 

[Luke M]

@SirJB7

If input month was 12, then the quarters end on 3,6,9,12. If input month is 8, quarters end on 11,2,5,8.


Now, to write a better formula... <grin>

=EOMONTH(A1,MOD(A2-MONTH(A1),3))


If using XL2003, make sure the Analysis ToolPak add-in is enabled.

 

[SirJB7]

Hy, Syerram!

Now that my ignorance has been enlightened by Luke M's light rays of wisdom, I should say that we both arrived to almost the same formula.

Regards!


@Luke M

Hi!

You provided my "CASFFML" perfect excuse. Thanks.

Regards!

 

[Syerram]

Thanks a lot LukeM and SirJB7! so much appreciated

 

[SirJB7]

Hi, Syerram!

Glad to help even if you couldn't solve it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!