Details about this thread
  • State Verified Answer
  • Replies 12 replies
  • Subscribers 322 subscribers
  • Views 7748 views
  • Users 0 members are here

How to calculate duration between two date/time fields?

Joseph Moore

I have two date/time fields (both will always be in the past) from which I need to calculate a duration. (i.e. the difference between one and the other.) Ideally, I could display it as hours:minutes, but I'll settle for just hours if I have to.

I tried this: 

abs(subtract(number($time_out_c),number($time_in_c)))

but it yields nothing. (a blank field)

I'm a complete new to using calculated fields. Any help would be appreciated.

Parents
  • 0

    Hello, Joseph.

    I would suggest you to do a diff between the two datetimes, and then use the "invert" method to check if there is positive difference between them.

    You can get more information on these links:

    PHP: DateTime::diff - Manual

    http://php.net/manual/en/class.dateinterval.php

    Here is an example:

    $interval = $datetime1->diff($datetime2);
if ($interval->invert) { // returns true if difference is positive
     if ($interval->h > 0) {
          return $interval->h; // h is for 'hour'
     } 
}

    Best regards,

    Ramon

  • 0 Sugar Employee Badge in reply to Ramon Marcondes

    Joseph,

    We have an existing feature request asking to make calculations between datetime fields more readable.  The description in the feature request contains an example formula.  I haven't fully tested it with your scenario but it may point you in the right direction.

    Hope this helps!

    Lori

  • 0 in reply to Lori F

    Thanks, Lori. That calculation is a beast, but I'll see what I can do. :-)

  • 0 in reply to Joseph Moore

    Hello All,

    We have used following calculation as a whole for minutes

    "multiply(subtract(multiply(subtract(divide(subtract(number(timestamp($chat_endtime1_c)),number(timestamp($chat_starttime1_c))),86400),floor(divide(subtract(number(timestamp($chat_endtime1_c)),number(timestamp($chat_starttime1_c))),86400))),24),floor(multiply(subtract(divide(subtract(number(timestamp($chat_endtime1_c)),number(timestamp($chat_starttime1_c))),86400),floor(divide(subtract(number(timestamp($chat_endtime1_c)),number(timestamp($chat_starttime1_c))),86400))),24))),60)"

    From this link: SugarCRM, Inc.

Reply
  • 0 in reply to Joseph Moore

    Hello All,

    We have used following calculation as a whole for minutes

    "multiply(subtract(multiply(subtract(divide(subtract(number(timestamp($chat_endtime1_c)),number(timestamp($chat_starttime1_c))),86400),floor(divide(subtract(number(timestamp($chat_endtime1_c)),number(timestamp($chat_starttime1_c))),86400))),24),floor(multiply(subtract(divide(subtract(number(timestamp($chat_endtime1_c)),number(timestamp($chat_starttime1_c))),86400),floor(divide(subtract(number(timestamp($chat_endtime1_c)),number(timestamp($chat_starttime1_c))),86400))),24))),60)"

    From this link: SugarCRM, Inc.

Children
No Data