How to calculate duration between two date/time fields?

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

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

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

Actually, when I attempt to save that formula, I receive this error: concat: All parameters must be of type 'string'

Hi Joseph Moore,

You haven't specified what version of Sugar you are on but in version 7.6.x there is a function for hoursUntil. This works the same as daysUntil where you simply wrap it around the date you check.

So to get a simple number of hours between you can "subtract(hoursUntil($date1),hoursUntil($date2))". This will give you your required number of hours difference.

If you require this to be split into hours and minutes then there are two viable options that I can see.

1) Create a second calculated field that formats the one we just discussed for hours only. This allows you to have a human readable field on the views and a pure hours field to be able to report on (I would suggest not having the format field reportable).

2) Have to field customised by code development either by yourself or by getting a quote from your Sugar partner  (if they cannot perform this then contact SugarCRM). This would allow you to have option 1 as just 1 field, meaning it stores the hours in the database and just formats the data when viewing the records. To my knowledge this should then give you the readability and reporting as the reports use database queries.

I hope this helps, and sorry for not screens hots but I done this on my phone.

Liam,

Thanks for the reply, but since the dates in question are always in the past, hourUntil returns 0.

Joseph Moore,

I recreated this on a demo system this morning and entered 26/01/2014 08:25am as my first date and 24/02/2014 09:25am as my second date, with my formula being "subtract(hoursUntil($date2),hoursUntil($date1))". And this returned me 697.00 hours as expected.

The only thing I can think of as to why your getting 0 is if I were to do my formula the other way round, so doing "subtract(hoursUntil($date1),hoursUntil($date2))" instead. This is as it would give you a negative number, which depending on how you setup your field may result in it showing as a 0 (for example if you set the minimum value allowed to be 0). In this case you could simply put the "abs" function around the entire formula, which would cause it to always be a positive number.

Joseph Moore,

I recreated this on a demo system this morning and entered 26/01/2014 08:25am as my first date and 24/02/2014 09:25am as my second date, with my formula being "subtract(hoursUntil($date2),hoursUntil($date1))". And this returned me 697.00 hours as expected.

The only thing I can think of as to why your getting 0 is if I were to do my formula the other way round, so doing "subtract(hoursUntil($date1),hoursUntil($date2))" instead. This is as it would give you a negative number, which depending on how you setup your field may result in it showing as a 0 (for example if you set the minimum value allowed to be 0). In this case you could simply put the "abs" function around the entire formula, which would cause it to always be a positive number.

Thanks, Liam.

I realized that the calculation does work as you describe. My problem was/is that the calculated field is not computed for existing records.

Is there a way to force Sugar to update all of my existing records? (UPDATE: Sorry, I see that I can update small batches through the list view, but will need to write a script to update all of the records.)

In Sugar 7 there is a feature called 'Recalculate Values' which will go through each selected option and update all calculated fields, allowing you to update 1000 records at a time maximum. If you have more than this many records then you can either do this in a few batches, or by exporting the data from your required module and then performing an import and update.