0

I have two columns in SharePoint that are each Single Line of Text columns that require the user to enter the start and end time of an audit, utilizing a 24-hour clock.

For example, the User enters "14:00" into start time and "14:26" into end time. I then created a Total Time Taken column that utilizes this formula:

=CONCATENATE(TEXT(HOUR([FMC Audit End Time]-[FMC Audit Start Time]),"#"),":",TEXT(MINUTE([FMC Audit End Time]-[FMC Audit Start Time]),"#"))

The problem is that if the start time is 14:00 and the end time is 14:05, it reflects the response as ":5".

How can I correct this formula so that if it is single digits, it will show as ":05"?

Is there a better way to capture this data? I did not want to capture the date, only the time.

Thank you,

Improve this question

1 Answer 1

Reset to default
1

Edit: Updated to match comments' requests

If you update your calculation to use the formula =TEXT(Start-End,"H:MM") (This will take the place of your entire existing formula), it will return 0:05instead of :5.

The TEXT formula doesn't play nicely with leading 0's unless a complete H:MM date/time format is used.

Improve this answer
4
  • Hi Vanblart - thank you for the response. I had tried this initially in my testing on this and that did not have an impact on the result. 14:10 start and 14:18 end time still resulted in :8 and not :08.
    – Bill
    Mar 28, 2018 at 12:34
  • Is it okay if instead of :05 it would display as 0:05?
    – vanblart
    Mar 28, 2018 at 12:49
  • Ok, I updated my answer to outline this instead! And just to make sure I was clear, the =TEXT function I provide will replace your entire =CONCATENATE( ... ) function :)
    – vanblart
    Mar 29, 2018 at 13:15
  • 1
    The "Text" code you provided above worked EXACTLY how needed. Thank you, Vanblart! I tried +1 the answer, but since I'm less than 15, it won't show publicly.
    – Bill
    Mar 30, 2018 at 15:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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