Calculated Field based on Two Dates

Hi Braedon Owen,

I was able to achieve this functionality with the following formula on a calculated checkbox field:

or(and(not(isAfter($start_date_c,now())),not(isBefore($end_date_c,today()))),and(not(isAfter($start_date_c,now())),not(greaterThan(strlen(toString($end_date_c)),0))))

Here are a few notes regarding the formula and behavior:

• $start_date_c and $end_date_c are both date fields. If you want to compare datetime fields, you would need to incorporate the concept presented in the Calculated Field - Converting Datetime to Date KB article.
• I use not() with isAfter() and isBefore() to simulate the <= and >= conditions you specify.
• The formula used to identify if the $end_date_c field is empty is taken from the Calculated Field - Determining if a Field Is Blank KB article
• There is a bug (54571 - you will need to be a support-authorized contact to view bug links) with the isAfter() function when comparing to today(). I mitigated that bug by incorporating the addDays() function, so that when the $start_date_c is the current day, the formula validates as expected. When this bug is fixed, remove the addDays() function. I mitigated this bug by using the now() function with isAfter().  Do not use now() with isBefore() because that will result in an improper validation for end dates equaling the current date. Sugar Support elected to create a new bug, 83499, to track the issue with using isAfter() and today(). Once that bug is fixed, you should replace now() with today().
• There is a bug (80890 possibly - I am validating with Sugar Support) where the checkbox will always appear checked while editing the record in question. Once you save the record, the checkbox will reflect the expected result. This issue is now mitigated with the use of now() for the isAfter() formulas. 

I hope this helps!

That works perfectly, thanks Chris!

I do have another question surrounding this; will the record automatically update itself to say inactive/active or would that be a process that is created?

for example, a promotion is created with start date tomorrow. When I save that, the checkbox for 'active' will be false, will it show active tomorrow automatically?

Hi Braedon,

In learning more about the defects with using isAfter() and today() together from Sugar Support, I was able to craft a better formula that improves the user experience. I updated the original post with that new formula and comments. 

Hi Braedon,

In learning more about the defects with using isAfter() and today() together from Sugar Support, I was able to craft a better formula that improves the user experience. I updated the original post with that new formula and comments.