From a date, derive which financial year it is in

[mikeopolo]

Hi

Given:
table1.financialyear eg 2005 (the year ending in ....)
table1.lastmonthofyear eg 6 (= June)

and

table2.date eg 01/07/2005 (dd/mm/yyyy)

how can I calculate the financial year of the date

eg with the above

01/06/2005 = 1st June 2005 = 2005 year
01/07/2005 = 1st July 2005 = 2006 year
Regards
Mike

 

[TamarGranor]

It's not clear to me whether you have a whole list of years in table1 or just the current year, but you'll want to do something involving putting the month and year out of the date in table2:

nMonth = MONTH(table2.date)
nYear = YEAR(table2.date)

Not sure what you should do next until I know exactly what's in table1.

Tamar

 

[craigsboyd]

I don't completely understand based on what you've posted what the date is that the new fiscal year starts, but I'll assume for this example that it starts on July 1st -- if it is not the first then you also need to check the Day(). Here is the example (untested, just written here)...

?GetFinancialYear(date()) && example of use

Function GetFinancialYear(tdDate)
   If Month(tdDate) > 6 && July or greater
      return (year(tdDate) + 1)
   else
      return (year())
   endif
endfunc

SweetPotato Software Website
My Blog

 

[mikeopolo]

Craig, thanks for this, I'll test it later today when I get back to this office.

Tamar, there's only one record in table 1, sorry I didn't make that clear. And in my example, Craig's assumption that the FY starts on 1st July is also correct.

Regards
Mike

 

[dbMark]

It can be reduced to this more compact statement, if it's okay for you:

? GetFinancialYear(date()) && example of use

Function GetFinancialYear(tdDate)
   return YEAR(tdDate) + IIF(MONTH(tdDate) > 6, 1, 0)
endfunc

 

[dbMark]

If the last month of the financial year could vary as any month of the year, this might do it:

? GetFinancialYear(DATE(), table1.lastmonthofyear)

Function GetFinancialYear(tdDate, tnLastMonth)
   return YEAR(tdDate) + IIF(MONTH(tdDate) > tnLastMonth, 1, 0)
endfunc

 

[mikeopolo]

Thank you all, always good to see different ways of coding. I used an iif(month(tdate)>lastmonth,year(tdate)+1,year(tdate)) within a select statement to do this.
Regards
Mike

 

[Olaf Doschke]

Hi!

Hmm, to me it seems just like a shift of N months, so simply shifting the financial year to the real year should do, shouldn't it?

? GetFinancialYear(DATE(), table1.lastmonthofyear)

Function GetFinancialYear(tdDate, tnLastMonth)
   Return Year(GoMonth(tdDate,12-tnLastMonth))
Endfunc

Why 12-tnLastMonth? Think of the special case, that the
financial year ends like the real year with December, then Gomonth(tdDate,12-12) means no shift, which is correct.

Does the financial year's end vary from year to year? Then it would be a little more complicated.

Bye, Olaf.

 

[mikeopolo]

Hi Olaf, thanks for your suggestions. It would be unusual for the FY to change, but it does happen. However, my accounting program cannot handle that totally accurately, so this will not be an issue in my present work. But a good point to remember!

Regards
Mike