Current fiscal year projection

[Rich]

I use the following to group by fiscal year without any problem but I need to get the difference in weeks between Date and the start of the current financial year
FYear: Year([TrDate])-IIf([TrDate]<DateSerial(Year([TrDate]),5,1),1,0)
Any ideas?

 

[Alexandre]

I am not sure I understand where your problem lies, Rich. Not in getting the difference in weeks between two days? [datediff("ww",Date1,Date2)]

 

[Rich]

Alex, are my posts indicating the approaching onslaught of senility
I guess I should have explained, I need a dynamic calculation.
I use a totals query to produce the output for a yearly graph grouped on my financial years, I have no problem with that, except that because my current financial year is only approx 12 weeks old the graph doesn't give an accurate reflection of the current years trading. I need to do some simple multiplication on the current years figures to get a projection on the graph.
I figure that if I can get the current week number of My financial year I will be able to do this.
The DatePart function appears to be able to do this, except that I can't figure out how to get it to recognise the 1st of May as the start of the year and not the 1st of January.
Any ideas?

 

[Alexandre]

What is the definitiion of the weeks you need to count for your projection: complete calendar weeks starting on Monday (they can be 51 or 52), just periods of 7 days (they are 52 + 1 or 2 days in a year), other?

 

[Rich]

Thanks Alex, I think this gives me what I need
FisWeek: DatePart("ww",Date())-DatePart("ww",#01/05#)
but I need to test it on my old notebook by changing the sysDate first, the actual number of weeks isn't critical, it's only for visual benefit, 1 week either way will not make much difference.
The only problem I have is that the help file says that by enclosing the Date literal without the year part in double quotes Access will automatically change the year, but I keep getting errors wherever I put the quotes.

 

[Alexandre]

Beware with the US date format in your formula.
You could create a public function to easily get the fiscal year at anytime:

Public Function FinancialYear (dDate as Date) as Integer
FinancialYear = Year(dDate)-IIf(dDate <DateSerial(Year(dDate),5,1),1,0)
End Function

Then, if you don't need much precision:

((Date() - DateSerial(FinancialYear (Date()),5,1))\ 7
or
DateDiff("ww", DateSerial(FinancialYear (Date()),5,1) , Date())

 

[Rich]

Thanks again Alex, sometimes it's just not possible to see the Wood for the Trees
I don't have any problems with dates thanks

 

[raskew]

And since there are numerous variations on the FY start month, it could be made a little more generic by allowing the user to specify the FY start month, rather than a hard-wired '5' for May, e.g.

Public Function FinancialYear(dDate As Date, FYStartMon As Integer) As Integer

FinancialYear = Year(dDate) - IIf(dDate < DateSerial(Year(dDate), FYStartMon, 1), 1, 0)

End Function

…to test
? FinancialYear(#03/01/01#, 6)
2000