Case with Dates

[Dominic Joannou]

I feel I am nearly there with the Case Function to define date periods, (UK date system), with:

 

Case (

(Date of Referral to Regulatory Body)   < "1/4/2008"; -3;  

(Date of Referral to Regulatory Body)   < "1/4/2009"; -2;  

(Date of Referral to Regulatory Body)   < "1/4/2010"; -1;  

(Date of Referral to Regulatory Body)   < "1/4/2011"; 0;  

(Date of Referral to Regulatory Body)   < "1/4/2012"; 1;  

(Date of Referral to Regulatory Body)   < "1/4/2013"; 2;    

(Date of Referral to Regulatory Body)   < "1/4/2014"; 3;  

(Date of Referral to Regulatory Body)   < "1/4/2015"; 4;  

(Date of Referral to Regulatory Body)   < "1/4/2016"; 5;  

(Date of Referral to Regulatory Body)   < "1/4/2017"; 6;  

(Date of Referral to Regulatory Body)   < "1/4/2018"; 7;  

(Date of Referral to Regulatory Body)   < "1/4/2019"; 8;  

(Date of Referral to Regulatory Body)   < "1/4/2020"; 9;  

(Date of Referral to Regulatory Body)  < "1/4/2021"; 10 )

 

Except it only gives - 3 as the result. Where am I going wrong?

[comment]

On 11/28/2019 at 12:30 PM, Dominic Joannou said:

Where am I going wrong?

You are comparing a date to a text string, so the comparison is done using alphabetical order. To compare date to date, change "1/4/2008", to GetAsDate ( "1/4/2008" ) or - preferably - Date ( 4 ; 1 ; 2008 ). That's if you really want to hardcode specific dates into your calculation.

 

Edited November 29, 2019 by comment

[Dominic Joannou]

I have tried your suggestions above. It still comes up with -3. I am trying to allocate a specific calculation to dates within ranges, not any singular record. This route seems flexible as date ranges to numbers can always be recalculated rather than doing anything manually.

 

If writing it out as a new calculation, what would one write in the filemaker field calculation. Many thanks Dominic 

[Wim Decorte]

15 minutes ago, Dominic Joannou said:

I am trying to allocate a specific calculation to dates within ranges, not any singular record.

Can you restate that?  Not sure I follow what you are trying to do with that.  "allocate" seems to imply that you are trying to set a result but the "not any singular record" means you are trying to update multiple records at once?  That doesn't work from a single calculation...

Is this calculation part of a calculated field definition, or part of a script?

[rwoods]

Hi Dominic

You could rewrite your code as follows. Assuming the pattern you have established so far carries on into the future, it will save you having to edit this calc in the future

Case (

//First case is true if date is prior to 1st April in its specific year

Month ( Date of Referral to Regulatory Body ) < 4 ; Year ( Date of Referral to Regulatory Body ) - 2011 ;

//Second case is true if date is on or after 1st April in its specific year

Year ( Date of Referral to Regulatory Body ) - 2010

)

 

Edited December 4, 2019 by rwoods

[comment]

1 hour ago, Dominic Joannou said:

I am trying to allocate a specific calculation to dates within ranges, not any singular record.

I too don't understand this part.