newbie date range calculations

[3mjh]

Hello,

I'm very new to filemaker, and am essentially trying to find date ranges using calculations, and then wether the record falls within that range determines what is displayed in the calculation field.

I have a date field, and a tax year field.

I want the tax year to show, e.g. "2011-12" if a record in the date field is between the range 06/04/2011 and 05/04/2012.

Any ideas how to do this using a calculation field or are there better ways of approaching this?

Any help would be much appreciated!

[mr_vodka]

I would use an additional table that stores the start and end date for your range with the text to display which in this case is "2011-12".

The relationship between the two tables would be keyed from the date field to the start date field of the other table using

datefield >= StartDate

datefield <= EndDate

[comment]

I want the tax year to show, e.g. "2011-12" if a record in the date field is between the range 06/04/2011 and 05/04/2012.

Does your fiscal year always begin on April 6?

[Peter Bouma]

I don't think you need another table/table occurrence, unless the fiscal year would have properties of its own to keep track of. But if you simply want to display it correctly (and assuming the boundary will remain 6 April for the time being), you could use:

Let([

theDate = myTable::transactionDate ; // insert your date field here

theYear = year( theDate ) ;

startFiscalMonth = 4 ;

startFiscalDay = 6

// or, preferably, global fields/global variables/fields from a utility table containing these numbers, to avoid hard-coding

];

If( theDate < Date( startFiscalMonth; startFiscalDay ; theYear ) ; // i.e. compare with 6 April the same year

GetAsText( theYear - 1 ) & "-" & Right( GetAsText( theYear ) ; 2 ) ; // display like 2012-13

GetAsText( theYear ) & "-" & Right( GetAsText( theYear + 1 ) ; 2 )

)

)

HTH, Peter