• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3755
  • Last Modified:

Microsoft Access function return financial year based on a date value

I am using Microsoft Access 2000 and I wish to write a function to return a financial year based on a date value.
The financial year should be computed as follows:
Let rdate be the date (e.g. receipt date)
Let fyear be the financial year

If the rdate ranges from

1-Apr-2005 to 31-Mar-2006
then fyear should be 20052006

1-Apr-2006 to 31-Mar-2007
then fyear should be 20062007


1-Apr-2007 to 31-Mar-2008
then fyear should be 20072008


1-Apr-2008 to 31-Mar-2009
then fyear should be 20082009


1-Apr-2009 to 31-Mar-2010
then fyear should be 20092010


1-Apr-2010 to 31-Mar-2011
then fyear should be 20102011

1-Apr-2011 to 31-Mar-2012
then fyear should be 20112012

and so on, let us say for the next 50years
I do not have data prior to 1-Apr-2005
0
Srinivas Mantha
Asked:
Srinivas Mantha
  • 3
  • 2
  • 2
  • +2
2 Solutions
 
cyberkiwiCommented:
function GetFinYear(dt as Date) as String
If Month(dt) >= 4 then
   GetFinYear = (Year(dt)-1) & Year(dt)
else
   GetFinYear = Year(dt) & (Year(dt)+1)
end if
end function
0
 
cyberkiwiCommented:
Wrong way around, sorry

Function GetFinYear(dt As Date) As String
If Month(dt) >= 4 Then
   GetFinYear = Year(dt) & (Year(dt) + 1)
Else
   GetFinYear = (Year(dt) - 1) & Year(dt)
End If
End Function
0
 
cyberkiwiCommented:
In a query, you can use this expression

fyear: Iif(Month(rdate) >= 4, Year(rdate) & (Year(rdate) + 1), (Year(rdate) - 1) & Year(rdate))
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Gustav BrockCIOCommented:
You may find use for this generic function which "shifts" a calendar date to a date of the financial year:
Public Function DateFinancial( _
  ByVal datDate As Date) _
  As Date
  
  ' Number of months from start of calendar year to start of financial year.
  Const clngMonthOffset As Long = 3
  
  Dim datFinancial      As Date
  
  datFinancial = DateAdd("m", -clngMonthOffset, datDate)
  
  DateFinancial = datFinancial
  
End Function

Open in new window


Now, armed with this, you can easily build your label for the financial year of a date:

strFinancialYear = Year(DateFinancial(rdate)) & Year(DateFinancial(rdate)) + 1

/gustav
0
 
JezWaltersCommented:
Are you sure you've got the date right for the start of each financial year - here in the UK each new financial year begins on April 6th (not April 1st)?

    http://en.wikipedia.org/wiki/Fiscal_year
0
 
JezWaltersCommented:
In case you're interested, you can determine the United Kingdom financial year like this:
Public Function fyear(rDate As Date) As String

    If Month(rDate) < 4 Or _
       (Month(rDate) = 4 And _
        Day(rDate) < 6) Then
        fyear = Year(rDate) - 1
    Else
        fyear = Year(rDate)
    End If
    fyear = fyear & fyear + 1

End Function

Open in new window

0
 
GRayLCommented:
In Canada, it goes 1Apr to 31Mar - actually you can start on any month, but it is always the first of the start month to the 31st of the previous month next year.
0
 
Srinivas ManthaConsultant Anesthesiologist and Pain PhysicianAuthor Commented:
Thanks for the prompt support that provoked solutions for some other countries as well
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now