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,

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.

  • 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 '18 at 12:34
  • Is it okay if instead of :05 it would display as 0:05? – vanblart Mar 28 '18 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 '18 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 '18 at 15:15

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.