datetime math
1 Topic
Help with Durations/TimSpans
Hello Everyone, Please can anyone help with a problem I'm having with durations? I'd like to have output like this: Who Task Average of JobLen Median Of JobLen Bill A 39:12 40:12 Bill B 37:37 37:49 John A 30:20 30:05 John B 26:42 26:39 My table, called Jobs looks like this: Who Task Start End Bill A 15/09/2021 12:54 17/09/2021 00:57 Bill A 20/09/2021 01:26 21/09/2021 17:39 Bill A 11/10/2021 00:25 12/10/2021 17:48 Bill B 11/09/2021 20:56 13/09/2021 09:25 Bill B 29/08/2021 19:49 31/08/2021 09:37 Bill B 29/08/2021 19:46 31/08/2021 09:37 Bill B 11/10/2021 03:23 12/10/2021 17:47 John A 04/10/2021 21:04 06/10/2021 01:39 John A 13/09/2021 11:41 14/09/2021 16:39 John A 11/09/2021 18:33 13/09/2021 01:44 John A 30/09/2021 01:26 01/10/2021 10:05 John B 02/08/2021 09:49 03/08/2021 12:19 John B 30/08/2021 09:33 31/08/2021 12:12 John B 30/09/2021 11:30 01/10/2021 14:28 My thoughts were that I'd have a Column for the duration that is a DateTime: JobLen = Jobs[End] - Jobs[Start] Then I'd use a Measure for the Average, that gets the average, gets the total seconds from that average and then formats the output the way I want it: AvgJobLenTxt = VAR avgJobLen = AVERAGE(Jobs[JobLen]) VAR seconds = -- I don't know how to convert to seconds 😞 VAR totalHours = INT(seconds / 3600) VAR totalMinutes = MOD(INT(seconds / 60), 60) RETURN RIGHT("0000" & totalHours, 4) & ":" & RIGHT("00" & totalMinutes, 2) As you can see, I don't know how to work out the total number of seconds from the result of the average function, I imagine I'll have the same problem with the Median function as well. Does anyone know how to do this, or am I approaching this from completely the wrong angle? I apologise for the length of the post, but I felt it was needed to give my question context. Thank you for taking the time to read. And a double thank you if you take the time to post.Solved1KViews0likes3Comments