Sunday 11 September 2011

Financial Date Formulas

Here are some financial date formulas that may come in handy.

To get the financial quarter use

=CHOOSE(INT((MONTH(A1)+2)/3),3,4,1,2)


To get the financial year use

=IF(MONTH(A1)<7,YEAR(A1)-1 &"-"& YEAR(A1),YEAR(A1) &"-"&YEAR(A1)+1)


Let me know if you have any alternatives.

5 comments :

  1. =YEAR(A1)-(MONTH(A1)<7) & "-" & YEAR(A1)+(MONTH(A1)>7)

    ReplyDelete
  2. =YEAR(EOMONTH(A1,-6))&"-"&YEAR(EOMONTH(A1,6))

    ReplyDelete
  3. Hmm, the first formula fails in the month of July. But I like the second one :-)

    ReplyDelete
  4. The first formula should be corrected in this way: YEAR(A1)-(MONTH(A1)<7) & "-" & YEAR(A1)+(MONTH(A1)>=7)

    ReplyDelete