Excel Tip – How to handle Financial Years that are not Calendar Years

October 23, 2019

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

 

Share on Facebook
Share on Twitter
Please reload

Featured Posts

I'm busy working on my blog posts. Watch this space!

Please reload

Recent Posts
Please reload

Archive
Please reload

Search By Tags
Please reload

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