Age Calculated Date when end date is empty

Hi Peter Rupp ,

To do an aging formula, you will use the ifElse formula to determine whether you end date field is populated and then perform your calculation based on that result. The formula would look like the following:

ifElse(
    equal(
        $end_date,
        ""
    ),
    subtract(
        0,
        daysUntil(
            $start_date
        )
    ),
    subtract(
        daysUntil(
            $end_date
        ),
        daysUntil(
            $start_date
        )
    )
)

There are a few caveats to keep in mind with this approach because Sugar Logic formulas are re-evaluated each time the record is saved:

1. If at least one of your fields is a datetime field, you can potentially get fluctuating calculations depending on when the datetime value occurred. For instance, if you use the date_entered field as your start date, then you may see variations on the start date depending on when it was created and who last saved the record. If a user in the eastern time zone created a record at 12:30 AM on October 21st, the formula will see the start date as 10/21 when the eastern time user saves the record but the formula will see it as 10/20 when a central time user saves the record. For calculating an integer value of days a record was open, it is recommended to use two date fields for these calculations to avoid these discrepancies. You can use SugarBPM to populate the date field on record creation with the current date.
2. Sugar Logic fields only recalculate on record saves so this solution will not give you an accurate record age for open records unless you are resaving those records in some fashion on a daily basis. We offer a plugin, Upsert Calculated Fields, which allows you to configure modules to be recalculated. Calculating record age is one of the most often reasons customers use our plugin. Please let contact us if you would like a demo!

I hope this helps!

Chris

Hi Peter Rupp ,

To do an aging formula, you will use the ifElse formula to determine whether you end date field is populated and then perform your calculation based on that result. The formula would look like the following:

ifElse(
    equal(
        $end_date,
        ""
    ),
    subtract(
        0,
        daysUntil(
            $start_date
        )
    ),
    subtract(
        daysUntil(
            $end_date
        ),
        daysUntil(
            $start_date
        )
    )
)

There are a few caveats to keep in mind with this approach because Sugar Logic formulas are re-evaluated each time the record is saved:

1. If at least one of your fields is a datetime field, you can potentially get fluctuating calculations depending on when the datetime value occurred. For instance, if you use the date_entered field as your start date, then you may see variations on the start date depending on when it was created and who last saved the record. If a user in the eastern time zone created a record at 12:30 AM on October 21st, the formula will see the start date as 10/21 when the eastern time user saves the record but the formula will see it as 10/20 when a central time user saves the record. For calculating an integer value of days a record was open, it is recommended to use two date fields for these calculations to avoid these discrepancies. You can use SugarBPM to populate the date field on record creation with the current date.
2. Sugar Logic fields only recalculate on record saves so this solution will not give you an accurate record age for open records unless you are resaving those records in some fashion on a daily basis. We offer a plugin, Upsert Calculated Fields, which allows you to configure modules to be recalculated. Calculating record age is one of the most often reasons customers use our plugin. Please let contact us if you would like a demo!

I hope this helps!

Chris