AnsweredAssumed Answered

Date range "If" calculation doesn't work

Question asked by StephenMallery7287 on Nov 26, 2011
Latest reply on Nov 28, 2011 by DavidJondreau


Date range "If" calculation doesn't work



Apart from my regular contact management needs (next contact dates, priorities, etc.), I have certain "big rock" events/tasks that HAVE to happen, or the consequences are catastrophic.  Some of these events aren't due until next year (for example, August 13, 2012).  Given the difficulty of remembering these specific important events, and wanting to keep them separate and more important than the rest of my scheduled contacts, I want Filemaker to alert me not only when that date arrives, but 1-7 days in advance, and 7-14 days in advance.  These are big deal events, and I want a heads-up as well as a target date reminder.

What I've Done

I created a separate date field for this type of critical event/task, and then a series of calculated fields that compare the date in the "big rock date" field with today's date.  These calculation fields are supposed to detect when the "big rock date" field is 7-14 days away, and produce a banner message at the top of my main layout, "BIG ROCK AHEAD."  When the date is 1-7 days away, another field is supposed to say, "BIG ROCK IS ALMOST HERE."  And on the due date, another says, "BIG ROCK DUE TODAY."  This all actually worked, until I upgraded from Filemaker 5.5 to 11.  Evidently FM has changed the way it handles date calculations (I have another date-based calculation solution elsewhere that also used to work but now doesn't).

What Works

     If(big rock due date = Get(CurrentDate), "BIG ROCK DUE TODAY", "")


     If(big rock due date < Get(CurrentDate), "BIG ROCK HAS PASSED!", "")

Those work great.

What Doesn't Work

Date ranges.  I won't replicate here everything I've tried.  Thought it might be better to simply ask, "How to define a date range equivalent to today's date + 1-7 days, and today's date + 8-14 days, and have it return the desired text result?

I tried a three-variable "If" calculation (today +7 < big rock due date < today +14).  Doesn't work.

I tried breaking it down into multiple two-variable If calculation fields, and then recombining them.  Can't get them to work.

I've made sure all the date-based calculations returned "date" results.

I've tried GetAsNumber(date field) with a number result -- which worked in FM 5.5 -- but that didn't work (can't seem to get 81312 to be greater than 121511, and 121511 + 7 = 121518).  Argh.

Blank results -- for example, If(big rock due date < today + 14, "BIG ROCK AHEAD", "") [note the "" at the end] -- are presumably considered "0" and hence are LESS THAN today.  Argh.

I tried fixing that through another calculation field that makes all zero results "12/31/2099," just to put it on the right side of the time line, but that won't work (can't seem to get it to recognize the blank as "0" or "").

Many of the results of my efforts produce multiple results for the same big rock due date, or illogical results (a future date as a past big rock date).

Jeesh, I've spent way too much time on this.

Bottom Line:

How do I define a calculation field to identify when a big rock due date is in the date range of (today + 2 to 7 days), or (today + 8-14 days), and return a specific text result?  I can't get this to work, and it's driving me crazy.