Extract financial Year from a transaction date

[RAM 1969]

Hi

I am looking for extracting financial year YYYY-YY out of transaction date in the format DD/MM/YYYY. For example, return the value 2013-14 from the transaction date 01/06/2013

[June7]

What is your financial year - July 1 to June 30?

Why is date in dd/mm/yyyy? Is it really stored in table in date/time field as mm/dd/yyyy? If it is then try:

Year(datefield) - IIf(Month(datefield)<7, 1,0) & "-" Trim(Format(datefield, "yy") + IIf(Month(datefield)>6,1,0))

If it isn't mm/dd/yyyy is it actually in a text field?

[orange]

Not sure exactly what you need but this sample may help.
http://support.microsoft.com/kb/210249

[dunc723]

The result will have to be a string. I would write a function that looks something like this:

Function FinYear(DateIn As Date) As String
Dim intYear1 As Integer, intYear2 As Integer, strFinYear As String
If Month(DateIn) >= 7 Then

intYear1 = Year(DateIn)

intYear2 = intYear1 + 1

Else

intYear2 = Year(DateIn)

intYear1 = intYear2 - 1

End If
strFinYear = intYear1 & "-" & Right(intYear2, 2) 'I always like to have a temporary result for debugging
FinYear = strFinYear
End Function

This function assumes the financial year runs from July 1 to June 30 (defined by ">=7" in the If statement). This is fine if it will always be the same - it saves you from having to specify it every time you use the function. If you need flexibility, add another input variable, something like this:

Function FinYear(DateIn As Date, StartMon As Integer) As String
Dim intYear1 As Integer, intYear2 As Integer, strFinYear As String
If Month(DateIn) > StartMon Then
...

Then if you have 03/06/2013 in datefield (today's date in dd/mm/yyyy) and your fiscal year starts April 1, your function in use will be
=FinYear (datefield, 4)
which will return the string 2013-14. If you entered FinYear(datefield, 7), the result would be 2012-13.

PLD

[RAM 1969]

Thank you
My financial year is April to March
Date filed is captured in the table as DD/MM/YYYY format as transaction data. I want to return the value in the new field as Financial Year like 2013-14. For example, if the date of transaction is 01.06.2013 (!st June, 2013), then the result should be 2013-14.

regards

RAM

[June7]

DD/MM/YYYY is not Access standard for storing date values. Date manipulation functions might not work properly with that structure. Did you review my post #2? Also review http://allenbrowne.com/ser-36.html

[dunc723]

DD/MM/YYYY is not Access standard for storing date values. Date manipulation functions might not work properly with that structure. Did you review my post #2? Also review http://allenbrowne.com/ser-36.html

Allen Browne says: "...just remember the user interface in Access uses the local Control Panel settings to interpret dates typed into the user interface."

Although Access can do some screwy things with dates if they are not entered in conformance with the user's Windows regional settings, it stores the date as a serial number. Today, June 5, 2013, is stored as 41430, not in any date format. The only risk is on the input side - if you have any concerns about this, you can force the user to put date, month, and year in separate entries, then combine them into the date that is actually stored in the table. Or you can have the form interpret the entry after the date field is updated but before the record is saved by displaying the date as text; for example, if the user enters 05/06/2103 (in dd/mm/yyyy format), you can have another box next to it that formulaicly converts it to "June 5, 2013" for the user to visually verify he entered it correctly.

[dunc723]

You can use the function I outlined above. Again, if you will always use April as the starting date for the financial year, change

If Month(DateIn) >= 7 Then

to

If Month(DateIn) >= 4 Then

Make sense? Are you familiar with writing custom functions?

[zoran.eremija]

I suggest you correct database model so that to add an entity Year. See the example attached.