Excel Tip – How to handle Financial Years that are not Calendar Years
As you’ve hopefully discovered, working with genuine Dates in Excel enables us to group Dates by Years, Quarters & Months. This is hugely beneficial when it comes to Filtering Data or Summarising it using Pivot Tables.
What can be frustrating though, is when you need to report by a Financial Year that differs from the standard Calendar Year. The grouping is all wrong!
Below are a couple of Fields that you can add to any Date Based Data that will enable you to group your data by your specific Financial (Fiscal) Year.
Based on cell B1 being the Numeric Year End Month (e.g. Feb=2, Jun=6 etc) with your standard Dates in Column A starting from Row 4, these are your Formula.
Fin Year End (B4) =IF(MONTH(A4)<=$B$1,YEAR(A4),YEAR(A4)+1)
Fin Yr Mth No. (C4) =MONTH(EOMONTH(A4,12-$B$1))
Month Name (D4) =TEXT(A4,"MMM-YYYY")
To download this Spreadsheet example as a starting point, click here