Monday, January 18, 2010

Fiscal Dates in Spreadsheets

Australian fiscal year 2008-2009 is from 1st July 2008 - 30th June 2009.

July is the 7th month.

LET'S HAVE EXCEL FUN! :(

Assuming A1 is you're date column, calculate year by ADDING 1 to the year for month's of July or later:

  EXCEL:
  = ( YEAR(A1) + (IF(MONTH(A1) >= 7, 1, 0) )

  OPEN OFFICE CALC:
  = ( YEAR(A1) + (IF(MONTH(A1) >= 7; 1; 0) )



The calculation for fiscal month:

  # EXCEL
  = INT( 1 + MOD( MONTH(A1) - 7, 12) )

  # OPEN OFFICE CALC
  = INT( 1 + MOD( MONTH(A1) - 7; 12) )



Last is fiscal quarter...

  # EXCEL
  = INT( 1 + (MOD(MONTH(A1)-7, 12) / 3)  )

  # OPEN OFFICE CALC
  = INT( 1 + (MOD(MONTH(A1)-7; 12) / 3)  )



NOTES:

- For US fiscal years, just replace '7' with '10' -- since October is your first fiscal month.

- The semicolon is used to separate function arguments because OpenOffice accepts numbers with commas as the decimal point when it is running in a location that uses that notation. If it used commas for lists as well, then something like =SUM(20,2,30,6) would be completely ambiguous.

No comments: