Cornerstone Software Ltd

The problem to solve is this: given a date (eg 1st September 2006), what financial year does it belong in?

A January to December financial year would make things simple, but this actually unusual. In New Zealand most common is the year ending 31st March, although many companies follow other conventions especially if they are part of a multinational corporation. See the Wikipedia article for more details.

I was surprised to not find a function for this in our company’s standard libraries, but I needed it, so I dusted off my brain and set out. And it wasn’t entirely obvious!

First up, how is the financial year defined? Because it is conventional to start a financial year on the first day of a month, my application defines twelve possibilities:

The value is stored in the database as an integer 1 to 12, representing the end month of the year. Hence “Year ending 31st March” would be stored as the value 3. There are more possibilities - I’ll get to them later on. I will mention however that at least one accounting system I am familiar with (MYOB) is limited to exactly these options.

The second question is how is an instance of a financial year represented? If your financial year is a calendar year then it is easy. Otherwise the convention (in NZ at least) is to either talk of the ‘2006/2007 year’ or use the year in which the period ends. So for the year ending 31-Mar-2007, that’s the ‘2007 financial year’. Ok.

I started out thinking of all sorts of mathematical cleverness to derive the end date from the date given, but quickly realised that there were only two possible financial years in which any given date could be: the one that ends in the same calendar year as that date, or the one that begins in the same calendar year. So I simply pick one of them, and if the date falls outside it, then it was the other one.

Because months have varying numbers of days, and February especially is pesky, it actually turned out easiest to use the start date for this test, which gives a function like this (C#):

/// <summary>
/// Given a date, return which financial year it belongs to
/// financialYearEndMonth = 1..12 (month in which FY ends)
/// </summary>
/// <param name="dtIn">Date to test</param>
/// <returns>Year in which the financial year ends</returns>
public int DateToFinancialYear( DateTime dtIn )
{
    // Determine starting date of the financial year which ends in
    // the same year as the given date
    DateTime dtFYStart
        = new DateTime( dtIn.Year, financialYearEndMonth%12+1, 1 );
    // given the date dtIn, it either lies in this financial year, 
    // or the one before it
    if( dtIn < dtFYStart ) { dtFYStart=dtFYStart.AddYears(-1); }
    return dtFYStart.AddYears(+1).AddDays(-1).Year;
}// DateToFinancialYear

Pages: 1 2

This entry was posted on Friday, September 1st, 2006 at 9:15 pm and is filed under .NET. You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.