SUBSCRIBE FOR FREE EXCEL TIPS

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)

Excel Advanced Webinar - Session 1 (4 Oct '19)

Picking up from where the Key Essentials Course left off, the objective of this course is to expose one to the many powerful advanced functions that Excel has to offer, as well as cover some key principles that ensure good practice while avoiding common pitfalls when working with data. Not forgetting all those useful tips and tricks to help maximize efficiencies and impress your colleagues! ​ Each Webinar will be recorded and made available to all Paying Participants for download. You’ll have access to all the course material through a secure login to our site.

Excel Tip – What to do with Leap Year Babies!?

In a previous Tip we showed you the simple solution of extracting Birthdays from a Date of Birth List. =Text(D4, “dd-MMM”) where D4 references a D.O.B. field. The problem though is that this only works 99.9% of the time. For most that would be good enough, unless you’re a Leap Year Baby! The issue is that on a Non-Leap Year, when you sort the Birthdays, the 29-Feb goes to the very bottom of the list, even after the 31-Dec. If you’re interested as to why this is so, see the explanation at the end of this post *. The Solution is to use a slightly more complicated Nested DATE function, with a hardcoded leap-year such as 2000. =DATE(2000,MONTH(D4),DAY(D4)) Apply a Custom Format of dd-MMM to remo

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