1

I am trying to use calculated columns to sum the difference between two times then multiply that result by a number.

The SharePoint list looks like this:

enter image description here

The "Start Time" and "End Time" columns are TEXT, the reason for this is because the list is being populated from Microsoft Form, which doesn't have a native time control so users are inputting this as free text.

"Time Diff" is a calculated column with the following calculation

=TEXT([Start Time]-[End Time],"h:mm")

This appears to be working as expected and is showing the time difference between the two time fields.

The next step is I want to multiply the result in "Time Diff" by the number in "Employee Count". Again this is a calculated column with the following formula

=[Time Diff]*[Employee Count]*24

As you can see it is working where the "Employee Count" is 2, but if it is 1 the sum isn't correct, the "Calculated Total Time" and "Time Diff" column should be the same. I am frustratingly close but I can't see to get this sum right, hopefully someone will be able to help?

Thanks in advance Rob

0

You should be able to achieve the goal with this formula:

=CONCATENATE(INT([Time Diff]*[Employee Count]*24),":",TEXT(MOD([Time Diff]*[Employee Count]*24*60, 60), "00"))
1
  • 1
    We posted our answers at the same time, your solution is very neat - thanks. – RobC_CTL Aug 18 '20 at 16:43
1

Ok, I can see the issue was between the seat and keyboard here. The "Calculated Total Time" is of course the decimal of the time, so 45 minutes = 0.75 of an hour so that calculation is correct.

Rob

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.