FY Date Range For Reports

[dbman]

I'm brain dead and felt so close to a likely easy solution. Excuse me if this is too wordy.

Have an invoice/receipt file and the line item area is a portal. The child file Item record's capture the date and invoice number of the related parent Invoice record.

Our fiscal year (FY) is not calendar but 7/1/01 to 6/30/02. I want to generate revenue reports for this off set FY. I thought I could would create a calc field "FY", which would be a nested If calc. To start I tried a non nested approach. Depending on date arrangement, or adding "" in different places I either had every date over a three year peroid indicate "FY 01" in the FY field or nothing. Examples:

1. If(Date >=7/1/2000 and Date <= 6/30/2001, "FY 01", ""

2. If(Date <=6/30/2001 and Date >=7/1/2000 , "FY 01", ""

3. If("Date <=6/30/2001 and Date >=7/1/2000" , "FY 01", ""

4. If("Date <=6/30/2001" and "Date >=7/1/2000" , "FY 01", ""

5. etc.

I'm trying to get the FY field to recognized the date of ecah new item record and provide the correct FY.

In the mean time, for my script, I perform a date range find for one FY set a global with FY 01, let's say, conduct a sort for the report and use the global in the Grand summary to identify the year of the report. This works OK for single reports but I have had to create numerous scripts in order to cover all FYs instead of one script that will generate all FYs.

Any ideas or other approaches?

Mike, and thanks

[BobWeaver]

I use the following calculation for my fiscal year

Year(TransactionDate)-(Month(TransactionDate)<7)

This will give you actual fiscal years of the format 2000, 2001 etc.

If you want FY 01, FY 02 etc., then do this:

"FY "& Right("00"& (Year(TransactionDate)-(Month(TransactionDate)<7)-1999),2)

The seven in the formula signifies that your fiscal year begins in the seventh calendar month.

[Edit: Corrected error in formula]

[ January 20, 2002: Message edited by: BobWeaver ]

[dbman]

Thanks Bob.

I'll give the two formulas a try.

Mike T