Difference between two dates and hours

Question

I have a list with three columns: Date 1, Date 2 (Both as date and time) and Difference (as a calculated column). I want get the difference between Date 1 and Date 2, but in hours and minutes. I found in TechNet this formula:

=IF(ROUND(((Data 2 - Data 1)*24-ROUNDDOWN((Data 2 - Data 1)*24,0))*60,0)=60,(ROUNDDOWN(((Data 2 - Data 1)*24),0)&": 00"),(ROUNDDOWN(((Data 2 - Data 1)*24),0)&":"&ROUND(((Data 2 - Data 1)*24-ROUNDDOWN((Data 2 - Data 1)*24,0))*60,0)))

But, if I have this scenario:

Date 1 as 31/07/2014 9:00 and Date 2 31/07/2014 9:05 the formula returns to me this result: 0:5

Is there any way to it returns 00:05?

Thanks in advance.

Answer 1

There was already a similar question, see here: Calculated field Date formula.


Wrap up your calculated field with the TEXT reference function Stuart has also linked in his post.

So in your case, it would look something like this:

=TEXT(IF(ROUND(((Data 2 - Data 1)*24-ROUNDDOWN((Data 2 - Data 1)*24,0))*60,0)=60,
(ROUNDDOWN(((Data 2 - Data 1)*24),0)&": 00"),(ROUNDDOWN(((Data 2 - Data 
1)*24),0)&":"&ROUND(((Data 2 - Data 1)*24-ROUNDDOWN((Data 2 - Data 1)*24,0))*60,0))),
"hh:mm")