Jump to content

Case with Date Calculations


This topic is 1420 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

I have an "Award Deadline" field that is calculated based on what is entered into another date field, "Allocation Date". The "Award Deadline" is typically 6 months from the "Allocation Date". Recently, due to COVID-19, "Allocation Date" entries in between October 2019 to June 2020 are allowed 12 months instead of the typical 6.

I am trying to fix/revise the Case in my "Award Deadline" field to allow entries of date between October 2019 to June 2020 in the "Allocated Date" field to calculate 12 months.

The case I have written for the "Award Deadline" is:

Case (

${Alloc. Date (CON) 1} ≤ GetAsDate("9/30/2019"); Date(Month(${Alloc. Date (CON) 1}) + 7;1; Year(${Alloc. Date (CON) 1}))  - 1;

${Alloc. Date (CON) 1} ≥ GetAsDate("07/31/2020"); Date(Month(${Alloc. Date (CON) 1}) + 7;1; Year(${Alloc. Date (CON) 1}))  - 1;

${Alloc. Date (CON) 1} ≥ GetAsDate("10/1/2019"); Date(Month(${Alloc. Date (CON) 1}) + 13;1; Year(${Alloc. Date (CON) 1}))  - 1;

${Alloc. Date (CON) 1} ≤ GetAsDate("06/30/2020"); Date(Month(${Alloc. Date (CON) 1}) + 13;1; Year(${Alloc. Date (CON) 1}))  - 1;

Date(Month(${Alloc. Date (CON) 1}) + 7;1; Year(${Alloc. Date (CON) 1}))  - 1

)

 

It of course doesn't work. I am still a beginner in Filemaker and am still learning Calculations. Any suggestions/help would be appreciated.

Link to comment
Share on other sites

My first suggestion would be to rename your field to a valid name, say AllocationDate. 

My second suggestion is to always use the Date() function to specify dates. GetAsDate("9/30/2019") works only for so long as your file uses the m/d/y date format; Date ( 9 ; 30 ; 2019 ) works universally.

Now, to add 12 months to allocation dates that fall between October 1, 2019 and June 30, 2020 (inclusive), and 6 months to all others, you could do:

Let (
m = If ( Date ( 10 ; 1 ; 2019 ) ≤ AllocationDate and AllocationDate ≤  Date ( 6 ; 30 ; 2020 ) ; 12 ; 6 )
;
Date ( Month ( AllocationDate ) + m ; Day ( AllocationDate ) ; Year ( AllocationDate ) )
)

You seem to want to push the date to the last day of the month, so that would be:

Let (
m = If ( Date ( 10 ; 1 ; 2019 ) ≤ AllocationDate and AllocationDate ≤  Date ( 6 ; 30 ; 2020 ) ; 12 ; 6 )
;
Date ( Month ( AllocationDate ) + m + 1 ; 0 ; Year ( AllocationDate ) )
)

--
Added:

AFAICT, the main reason why your attempt doesn't work is the order of tests. The Case() function exits at the first test that evaluates as true, so you would need to order your tests chronologically, e.g.

Case (
AllocationDate < Date ( 10 ; 1 ; 2019 ) ; 7 ;
AllocationDate ≤ Date ( 6 ; 30 ; 2020 ) ; 13 ;
7 )

 

 

Edited by comment
  • Like 1
Link to comment
Share on other sites

  • Newbies

I've tried what has been suggested, but it doesn't seem to work the way I'd like. After inserting the formula and tested with an AllocationDate of "10/01/2019" the result is still "4/30/2020". Entries from first ten days of October only calculates a 6 month deadline instead of 12. When entered "10/10/2019" it then calculates a 12 month deadline. Also, regardless of the year entered, the result would calculate 12 months in any dates in October - June 30, but I only want AllocatioDate's in between October 2019 - June 2020

These are my tested results:

AllocationDate AwardDeadline Desired End Result
10/01/2019 04/30/2020 10/31/2020
10/01/2018 04/30/2020 04/30/2020
10/10/2018 10/31/2019 04/30/2020
06/15/2020 12/31/2020 06/31/2021
07/01/2020 01/31/2021 07/31/2021
10/15/2020 10/31/2021 04/40/2021

 

Link to comment
Share on other sites

You must be using some other calculation, because I am getting results that are very different from yours:

  • for AllocationDate of 10/01/2019 my result is 10/31/2020 (as expected);
  • for AllocationDate of 10/01/2018 my result is 04/30/2019 (as expected - assuming you didn't really mean 2020);
  • for AllocationDate of 10/10/2018 my result is 04/30/2019 (as expected- assuming you didn't really mean 2020);
  • for AllocationDate of 06/15/2020 my result is 06/30/2021 (almost what you expected - there is no June 31);
  • for AllocationDate of 07/01/2020 my result is 01/31/2021;
  • for AllocationDate of 10/15/2020 my result is 04/30/2021(as expected - assuming 40 is a typo).

So the only difference is for AllocationDate of 07/01/2020 where you say it should add a year and I say it should be 6 months because the AllocationDate is in July 2020 and therefore NOT between October 2019 - June 2020. 

 

1 hour ago, Dancie said:

Also, regardless of the year entered, the result would calculate 12 months in any dates in October - June 30

That is simply not so. As evidenced by the case where AllocationDate is 10/01/2018 (2nd example above).

 

Edited by comment
Link to comment
Share on other sites

  • Newbies

I found the issue. The AllocationDate field was set as "Text" and not "Date". Once I changed it to a "Date" field, it worked fine.

Thanks for your patience with me.

Edited by Dancie
Link to comment
Share on other sites

This topic is 1420 days old. Please don't post here. Open a new topic instead.

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
×
×
  • Create New...

Important Information

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