0

I need three columns in a custom list that shows time only - such as check-in time; 06:00:00 and checkout time; 06:45:24, I need to calculate the time differences in the third columns such as 00:45:24 - I need the third columns formate time as [hh]:mm: ss for analysis purposes if I need to show overall difference time such as the total time spend in whatever a place.

  • SharePoint is not good with time differences like these, and AFAIK it cannot format times with values over 24 hrs. If you want to analyse the data, the easiest way may be to grab the data with Power Query in Excel and run your analysis there. Excel was made for stuff like that. SharePoint wasn't. – teylyn Apr 14 at 20:54
  • Are the square brackets and the extra space needed in the third column? "[hh]:mm: ss" – Mike Smith - MCT - MVP Apr 14 at 20:57
  • ok no need to over 24 hours, I will analysis it via PowerBI (once, I update the list, the power BI dashboard will change as the collected time) but I want the time formate to be in a column as hh:mm: ss – Red John Apr 14 at 21:09
0

Pretty easy...

  • Create two "Single line of text" columns named Check In and Check Out.
  • Optionally add a validation formula to ensure the users enter a proper time string.
  • Add a Calculated column with a formula similar to this:

    =TEXT( TIMEVALUE( [Check Out] ) - TIMEVALUE( [Check In] ), "hh:mm:ss" )

Depending on the type of analysis you need, you might add an additional Calculated Column that just returns the numeric value. Set the return type to Number and you will get a fraction of a day. (1 = 24 hours, .5 = 12 hours, etc)

=TIMEVALUE( [Check Out] ) - TIMEVALUE( [Check In] )

Or to get the result in hours:

=( TIMEVALUE( [Check Out] ) - TIMEVALUE( [Check In] ) ) * 24

enter image description here

  • still not clear, for example, a person enters a place (hotel) at (20:00:00 as Check-in) then left at (22:30:00 as Checkout) the calculated time remains is (02:30:00 as host time in the place) i need this to calculated KPI of in house time residency – Red John Apr 14 at 21:46
  • You will need to explain what "calculated KPI of in house time residency" means. – Mike Smith - MCT - MVP Apr 14 at 21:50
  • more example; i need to calcalate police patrol response time, as dispatch time then arraval time. [Dispatch time] - [ arrived time]=[patrol KPI] as all columns should be in hh:mm:ss formate – Red John Apr 14 at 21:56
  • and please help in How can i add a validation formula to ensure the users enter a proper time string? – Red John Apr 14 at 21:58
0

and please help in How can i add a validation formula to ensure the users enter a proper time string?

Several ways... this one checks for a valid time:

valid: 1 PM, 1:05 PM, 1:05:10 PM, 1:05:10.123 PM, 3/1/2019, 3/1/2019 4 PM

not valid: 1, abc, PM, 1PM, Tuesday, noon

=NOT(ISERR(TIMEVALUE([Check Out])))

This checks to see if it is a valid time, and they typed all of the digits and the AM/PM:

valid: 01:15:00 PM

not valid: 1, abc, 1:15:00 PM, 01:15 PM, 01:15:00, PM, 1PM, Tuesday, noon

=AND(LEN([Check Out])=11,NOT(ISERR(TIMEVALUE([Check Out]))))

If you are using 24 hour times (no AM/PM) then use this:

valid: 01:15:00, 20:10:00

not valid: 1, abc, 01:15:00 PM, 01:15 PM, 01:15:00, PM, 1PM, Tuesday, noon

=AND(LEN([Check Out])=8,NOT(ISERR(TIMEVALUE([Check Out]))))
  • an error message was showing A formula has a syntax error. please write it again Smith. – Red John Apr 15 at 1:16

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.