Date range within a calculation

[davidnickerson]

I'm trying to create a simple calculation that returns 1 (true) if a date range condition is met.  Specifically, I'm looking for a true result if the date in a given field ("Date") is between 10/5/16 and 12/31/16.

I figured this syntax should do it:

If ( Date > 10/5/16 and Date < 12/31/16 ; 1 ; 0 )

However, this returns false for all records within the date range.  I'm able to at least get true results for different calculations, such as If (Date > 10/5/16 ; 1 ; 0 ) and even If ( Date > 10/5/16 or Date < 12/31/16 ; 1 ; 0 ) but not If ( Date > 10/5/16 and Date < 12/31/16 ; 1 ; 0 ).

Any help is appreciated.

Thanks.

Edited November 18, 2016 by davidnickerson

[webko]

First, the above would only work for 6 Oct through to 30 Dec (due to greater than / less than)

Make sure Date really is a date field, not text. 

 

[davidnickerson]

1 hour ago, webko said:

First, the above would only work for 6 Oct through to 30 Dec (due to greater than / less than)

Make sure Date really is a date field, not text. 

 

Right, that's what I want:  A true result for all records where the date is 10/6/16...12/30/16.  I have 11 records in my database within that date range.  The calculation above returns null ("") for all these records, when it should return "1".  The Date field in question is a calculation field with the result being date.  Again, other calculations work for > only, or for > "or" <, but not > "and" < (dates within the range).

 

Edited November 18, 2016 by davidnickerson

[webko]

Is it an unstored related field calculation?

The results from those can be interesting in more modern versions of Filemaker

[davidnickerson]

24 minutes ago, webko said:

Is it an unstored related field calculation?

The results from those can be interesting in more modern versions of Filemaker

Yes, unstored, and the date calc is from a related field.  I'm using FM 12 Advanced.

Edited November 18, 2016 by davidnickerson

[webko]

In Find mode, the relationship can become invalid (depending on context) and result in no results.

Finding on unstored calc also causes efficiency issues when enough records are present.

Consider setting a local date field via scripting when a child record is created / updated to be searched on.

[doughemi]

I got it to work with 

date > Date(10;5;2016) and date < Date(12;31;2016)

But even that didn't work unless the year is 4 digits.

[davidnickerson]

15 minutes ago, webko said:

In Find mode, the relationship can become invalid (depending on context) and result in no results.

Finding on unstored calc also causes efficiency issues when enough records are present.

Consider setting a local date field via scripting when a child record is created / updated to be searched on.

I'm not in Find Mode.  There is no finding of related records being done here.  Just a calculation.

 

7 minutes ago, doughemi said:

I got it to work with 

date > Date(10;5;2016) and date < Date(12;31;2016)

But even that didn't work unless the year is 4 digits.

Hmm.  Interesting; that worked for me, too.  But can somebody tell me why this syntax: 

If ( Date > 10/5/16 and Date < 12/31/16 ; 1 ; 0 )

is not correct?

[doughemi]

I don't know, but I've gotten to the point that I let FileMaker do the heavy lifting (with the Date( ) function) on anything having to do with dates.

[davidnickerson]

Thanks for your help!

[doughemi]

This works, too:

date > "10/5/2016" and date < "12/31/2016"

Edit: could it be that FM is trying to do the division in your original calc? 

Edited November 18, 2016 by doughemi

[comment]

3 hours ago, davidnickerson said:

can somebody tell me why this syntax: 

If ( Date > 10/5/16 and Date < 12/31/16 ; 1 ; 0 )

is not correct?

Because 10/5/16 means 10 divided by 5 divided by 16, which evaluates to 0.125 - and every date is greater than that.

 

3 hours ago, doughemi said:

This works, too:

date > "10/5/2016" and date < "12/31/2016"

I doubt that very much. What makes you think it works? I believe the comparison will be made in the string domain, causing a date of 9/5/2016 to be evaluated as greater than "10/5/2016".

---

I would also ask why it's necessary to hard-code a date range into a calculation formula. What will you do in the year 2017 and beyond?

 

Edited November 18, 2016 by comment

[doughemi]

6 hours ago, comment said:

What makes you think it works?

Inadequate testing. Tests with a number of dates worked ok (including 9/5/2016) but others fail (like 12/29/2015).

[davidnickerson]

17 hours ago, comment said:

I would also ask why it's necessary to hard-code a date range into a calculation formula. What will you do in the year 2017 and beyond?

Duly noted.

Eventually I was planning to write calcs for Fiscal Quarters, so that the various date ranges would return "Q1", "Q2", etc.  But I knew this would become an issue in future years, so this was only an interim solution.  Thankfully, I found a good Fiscal Quarter custom function on Brian Dunning's page which took care of things nicely.

Your explanation of the issue with the date syntax was appreciated.