Date Calculation

[kshelton]

Fields: Date, Tax Year

Please note dates are d/m/y as I am in UK.

I want Tax Year entry to be automatic depending on Date,

e.g. Tax Year '2009-2010' would apply to all records dated 6-4-2009 to 5-4-2010.

When I tried a Calc it did not work. I then experimented with a script using single dates instead of a range and found that my Calc (Date < "5/4/2009") is merely operating on the first digit and therefore triggers dates starting with a digit below 5 and ignores month and year,

e.g. ignores 6/4/2008, 7/6/2010 but triggers 13/4/2008 and 3/5/2010.

I tried a simple find for the range of dates and this worked.

I have searched but cannot see what I need to do to make my Calc respect the date.

Help!

Thanks

Keith

[TheTominator]

If your field is named TheDate and your date is in d/m/y, then

theDate < Date(4; 5; 2009)

is what you need when making a simple comparison.

Put together multiple comparisons with parentheses and "and", e.g. ( a < and ( a > c)

The Date() function takes month, day, year values and converts them into an internal FileMaker date.

See also Day(), Month(), Year() functions for extracting those pieces as numbers from a date.

Edited September 20, 2010 by Guest

[comment]

Try =

Let ( [

y =  Year ( Date ) ; 

t = y - ( Date < Date ( 4 ; 6 ; y ) 

)

] ;

t & "-" & t + 1

)

Result type should be Text.

This should work foe ANY year - assuming your tax year always starts on April 6th.

Edited September 20, 2010 by Guest

[kshelton]

Thanks very much to both.

I have managed to set it up so that it applies automatically to any year.

Just what I wanted.

Thanks again.

Keith