Replied 8 September 2017, 3:19 pm EST
Thanks for the sample code. We are sorry to mention but we are unable to replicate the ‘rounding off the difference’ behavior of ‘DATEDIF’ function. Please refer to the attached video(DateDif Behaviour.gif) which clearly shows that ‘DATEDIF’ function returns the absolute difference of days between the two dates(without rounding it off) just like that in Microsoft Excel. Please correct me if I missed something in order to replicate the problem.
However, a comparatively simple and correct formula to get the difference between the dates in ‘hh:mm’ format is:
IF(DATEDIF($B1,$A1,"D")>0,DATEDIF($B1,$A1,"D"),DATEDIF($A1,$B1,"D"))*24 +HOUR(DATEVALUE($A1)-DATEVALUE($B1)) & ":" & IF(SECOND(DATEVALUE($A1)-DATEVALUE($B1))<30,MINUTE(DATEVALUE($A1)-DATEVALUE($B1)),MINUTE(DATEVALUE($A1)-DATEVALUE($B1))+1)
In simple words,
(Difference Between Days *24 + Difference between Hours) : Difference between Minutes
This formula uses DATEVALUE
function which returns the DateTime object of Specified Date. Subtraction between two DateTime objects
returns ‘TimeSpan’ in format of ‘dd hh:mm’. Here, the number of days are converted into hours(using DATEDIF) whereas Hours and Minutes are used as it is.
Please refer to the attached modified sample(test_formula&databind3_Modified.zip) and Video(Correct Output.gif) implementing the same.
Hope it helps.
Reeva 2015/09/Correct-Output.gif 2015/09/test_formuladatabind3_Modified.zip 2015/09/DateDif-Behaviour.gif.zip