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

Featured Posts
Posts Are Coming Soon
Stay tuned...
Recent Posts
Archive
Search By Tags

Training Offices:

Johannesburg

106 Johan Avenue, Sandton

Durban

1 Tamarind Close, Umhlanga

Pietermaritzburg

400 Old Howick Road, Hilton

Corporate Onsite Training Available Countrywide

  • Summit Solutions Excel Training
  • Summit Solutions Excel Training
  • Summit Solutions Excel Training

Tel: 086 167 3923

Email: info@summitsolutions.co.za 

Head Office: Hilton Quarry Office Park, 400 Old Howick Road, Hilton.

2020 | Copyright | Summit Solutions