Details about this thread
  • State Not Answered
  • Replies 2 replies
  • Subscribers 320 subscribers
  • Views 161 views
  • Users 0 members are here
Related content from around SugarClub

Calculate age between today or start and end dates

Peter Rupp

I would like to calculate the age (number of days) between start and end dates or today's date and the start date.

I need to check if the start date is populated first.

If so, then check if the end date is populated.

If end date exists, then calculate the number of days between the end date and the start date.

If end date does not exist, then calculate the number of days between today and the start date.

If start date does not exist, then ""

  • 0 Sugar Employee Badge

    Hello Peter, 

    Could you please check if this works on your end?

    It worked in my tests, but I want to make sure I’ve correctly captured all your requirements.


    ifElse(
  not(equal($date_start, "")),
  ifElse(
    not(equal($date_end, "")),
    abs(subtract(daysUntil($date_end), daysUntil($date_start))),
    abs(daysUntil($date_start))
  ),
  ""
)


    This is what the formula is doing: 

    Check if date_start is populated: If it’s empty, the formula returns an empty string

    If date_start is populated checks if date_end is populated: 

    yes: Calculate the absolute difference in days between date_end and date_start

    If no: Calculate the absolute number of days from date_start to today with the daysUntil.

    Is this what you need? 

    Cheers, 

    André 



  • 0

    Keep in mind that calculated fields only evaluate when the record is saved, so anything relative to "today" will not be updated each day.

    To update something each day you will need a scheduled job that runs every day.

    FrancescaS