Calculated Field based on Two Dates

Question

I'm trying to build a formula that says if start date field is <= today & end date field is >= today or start date field is <=today & end date field is black then active checkbox = true

Braedon Owen · Accepted Answer

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!