Jump to content
Sign in to follow this  
davidnickerson

Date range within a calculation

Recommended Posts

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 by davidnickerson

Share this post


Link to post
Share on other sites

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. 

 

Share this post


Link to post
Share on other sites
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 by davidnickerson

Share this post


Link to post
Share on other sites

Is it an unstored related field calculation?

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

Share this post


Link to post
Share on other sites
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 by davidnickerson

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by doughemi

Share this post


Link to post
Share on other sites
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 by comment
  • Like 1

Share this post


Link to post
Share on other sites
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).

Share this post


Link to post
Share on other sites
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.

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  

  • Who Viewed the Topic

    1 member has viewed this topic:
    doughemi 
×

Important Information

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