Jump to content
Sign in to follow this  
3mjh

newbie date range calculations

Recommended Posts

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!

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.