1

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.

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")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.