1

Working within SP, I have two columns Tech Checkin and Tech Checkout with a third column titled Visit Duration.

The tech checkin and check out columns will contain a time only and are set as single line of text (I do not want a date populated in that column). The Visit Duration column should take the checkin and checkout times and create a sum equal to duration.

For example, tech check in is 12:00 PM and tech checkout is 1:27 PM. I want the Visit Duration column to read 1:27.

Is there a way to do this?

2

SharePoint doesn't have parsing functions to automatically convert text to times. I'd recommend using a datetime field anyway, as that prevents data entry issues as well as makes the time calculation much simpler. If you must allow users to enter text, a developer could add an event handler or implement an Azure function to parse the string and perform the calculation.

Or, for brittle solution that will break frequently:

=(TIME(left([txtTime],2),mid([txtTime],4,2),"00")-TIME(left([txtTime2],2),mid([txtTime2],4,2),"00"))*1440

The above will require times to be entered with leading zeroes, as:

06:00 am

And that's what a meant by brittle, if someone enters the time inconsistently, the function will throw an error. You'll also need to add an if statement to check for am/pm to conditionally add 12 hours to the first argument.

1

Using the following forumla:

INT(([Tech Checkout]-[Tech Checkin])*24)&":"&MOD(INT(([Tech Checkout]-[Tech Checkin])*1440),60)

enter image description here

Result: Tech Checkin and Tech Checkout are single line of text fields

enter image description here

Calculated Field Formulas

Calculated Field Formulas with demos

Note: If the answer helps you, don't forget to accept it as the best answer. It will help others find the answer/solution quickly

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.