1

I have a custom list in SharePoint with two Date and Time columns, namely StartTime and EndTime. Now I want to calculate the difference between them, so I created a new calculated column Length and entered the formula like this:

=INT(([EndTime]-[StartTime])*1440)

It should return the duration between StartTime and EndTime in minutes. But in some situations, the result is off by 1. E.g. 8:30PM - 6:30PM = 119 minutes. I believe this must have been caused by floating point rounding error. But how do I prevent it? Or should I use a formula other than INT()?

Edit: I saw this exact same formula here, under Date and time formulas > Calculate the difference between two times > Total minutes between two times. But that only added to my confusion, why is something Microsoft used as an example not working as intended?

1

Finally, I got a hint from a colleague, the solution is really simple:

Change the formula to

=([EndTime]-[StartTime])*1440

And then set decimal place to 0. SharePoint will now round off the number correctly.

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.