How can I show total duration?

[dan205]

How can I show the total duration for all the rows in this table? The last column gives the individual durations but there's no total in the total row.

Thanks,
Dan

It is filtered on a date:

[Abhishekb]

Hi @dan205 ,

Can you share this data in text, so that I can work on that and say you the result?

[dan205]

Hi Abhishek,

Here are the first 40 rows of data, comma-delimited.

Thanks for looking at this.

Dan

Start DateTime,"[Start Date]
//Date([Start DateTime])","[Start Time]
//Time(Frac([Start DateTime]), 'hh:mm:ss')","[End DateTime]
//Time(Frac([End DateTime]), 'hh:mm:ss')","[End Date]
//Date([End DateTime])","[End Time]
//Time(Frac([End DateTime]), 'hh:mm:ss')",=Interval([End Time] - [Start Time])
2023-09-14 03:43:41.000,14/09/2023,03:43:41,2023-09-14 03:44:21.000,14/09/2023,03:44:21,0:00:40
2023-09-14 03:44:29.000,14/09/2023,03:44:29,2023-09-14 03:45:05.000,14/09/2023,03:45:05,0:00:36
2023-09-14 03:44:29.000,14/09/2023,03:44:29,2023-09-14 03:45:15.000,14/09/2023,03:45:15,0:00:46
2023-09-14 03:44:29.000,14/09/2023,03:44:29,2023-09-14 03:45:16.000,14/09/2023,03:45:16,0:00:47
2023-09-14 03:45:16.000,14/09/2023,03:45:16,2023-09-14 03:45:20.000,14/09/2023,03:45:20,0:00:04
2023-09-14 03:45:16.000,14/09/2023,03:45:16,2023-09-14 03:45:21.000,14/09/2023,03:45:21,0:00:05
2023-09-14 03:45:16.000,14/09/2023,03:45:16,2023-09-14 03:45:22.000,14/09/2023,03:45:22,0:00:06
2023-09-14 03:45:16.000,14/09/2023,03:45:16,2023-09-14 03:45:23.000,14/09/2023,03:45:23,0:00:07
2023-09-14 03:45:16.000,14/09/2023,03:45:16,2023-09-14 03:45:24.000,14/09/2023,03:45:24,0:00:08
2023-09-14 03:45:28.000,14/09/2023,03:45:28,2023-09-14 03:45:28.000,14/09/2023,03:45:28,0:00:00
2023-09-14 03:45:28.000,14/09/2023,03:45:28,2023-09-14 03:45:29.000,14/09/2023,03:45:29,0:00:01
2023-09-14 03:45:32.000,14/09/2023,03:45:32,2023-09-14 03:45:33.000,14/09/2023,03:45:33,0:00:01
2023-09-14 03:45:40.000,14/09/2023,03:45:40,2023-09-14 03:46:12.000,14/09/2023,03:46:12,0:00:32
2023-09-14 03:45:40.000,14/09/2023,03:45:40,2023-09-14 03:46:13.000,14/09/2023,03:46:13,0:00:33
2023-09-14 03:45:40.000,14/09/2023,03:45:40,2023-09-14 03:46:17.000,14/09/2023,03:46:17,0:00:37
2023-09-14 03:46:27.000,14/09/2023,03:46:27,2023-09-14 03:46:28.000,14/09/2023,03:46:28,0:00:01
2023-09-14 03:46:27.000,14/09/2023,03:46:27,2023-09-14 03:46:31.000,14/09/2023,03:46:31,0:00:04
2023-09-14 03:46:27.000,14/09/2023,03:46:27,2023-09-14 03:46:36.000,14/09/2023,03:46:36,0:00:09
2023-09-14 03:46:46.000,14/09/2023,03:46:46,2023-09-14 03:46:52.000,14/09/2023,03:46:52,0:00:06
2023-09-14 03:46:46.000,14/09/2023,03:46:46,2023-09-14 03:46:53.000,14/09/2023,03:46:53,0:00:07
2023-09-14 03:46:46.000,14/09/2023,03:46:46,2023-09-14 03:47:01.000,14/09/2023,03:47:01,0:00:15
2023-09-14 03:47:02.000,14/09/2023,03:47:02,2023-09-14 03:47:03.000,14/09/2023,03:47:03,0:00:01
2023-09-14 03:47:04.000,14/09/2023,03:47:04,2023-09-14 03:47:07.000,14/09/2023,03:47:07,0:00:03
2023-09-14 03:47:11.000,14/09/2023,03:47:11,2023-09-14 03:47:12.000,14/09/2023,03:47:12,0:00:01
2023-09-14 03:47:16.000,14/09/2023,03:47:16,2023-09-14 03:47:19.000,14/09/2023,03:47:19,0:00:03
2023-09-14 03:47:21.000,14/09/2023,03:47:21,2023-09-14 03:47:21.000,14/09/2023,03:47:21,0:00:00
2023-09-14 03:47:21.000,14/09/2023,03:47:21,2023-09-14 03:47:22.000,14/09/2023,03:47:22,0:00:01
2023-09-14 03:47:26.000,14/09/2023,03:47:26,2023-09-14 03:47:26.000,14/09/2023,03:47:26,0:00:00
2023-09-14 03:47:33.000,14/09/2023,03:47:33,2023-09-14 03:47:44.000,14/09/2023,03:47:44,0:00:11
2023-09-14 03:47:33.000,14/09/2023,03:47:33,2023-09-14 03:47:46.000,14/09/2023,03:47:46,0:00:13
2023-09-14 03:47:33.000,14/09/2023,03:47:33,2023-09-14 03:47:51.000,14/09/2023,03:47:51,0:00:18
2023-09-14 03:47:51.000,14/09/2023,03:47:51,2023-09-14 03:47:52.000,14/09/2023,03:47:52,0:00:01
2023-09-14 03:47:59.000,14/09/2023,03:47:59,2023-09-14 03:48:00.000,14/09/2023,03:48:00,0:00:01
2023-09-14 03:47:59.000,14/09/2023,03:47:59,2023-09-14 03:48:01.000,14/09/2023,03:48:01,0:00:02
2023-09-14 03:47:59.000,14/09/2023,03:47:59,2023-09-14 03:48:02.000,14/09/2023,03:48:02,0:00:03
2023-09-14 03:47:59.000,14/09/2023,03:47:59,2023-09-14 03:48:06.000,14/09/2023,03:48:06,0:00:07
2023-09-14 03:48:08.000,14/09/2023,03:48:08,2023-09-14 03:48:09.000,14/09/2023,03:48:09,0:00:01
2023-09-14 03:48:17.000,14/09/2023,03:48:17,2023-09-14 03:48:22.000,14/09/2023,03:48:22,0:00:05
2023-09-14 03:48:17.000,14/09/2023,03:48:17,2023-09-14 03:48:24.000,14/09/2023,03:48:24,0:00:07

[Abhishekb · Accepted solution]

Hi @dan205 ,

I think this is what you required,

You can do this is 2 ways.

1st way is sum(Difference) where take Difference as measure . Check below,

and 2nd way is using formula,

=Interval(Sum(If([End Time] >= [Start Time], [End Time]-[Start Time], MakeTime(24)-[Start Time]-[End Time])), 'hh:mm:ss')

I think, I have answered your question.

[dan205]

Hi Abhishek,

It works but I get some unexpected totals for some rows. See rows 3 and 6, for examples:

I will take a look at the data and try to work out what is happening.

Many thanks for your help.

Dan

[Abhishekb]

Hi @dan205 ,

I think there is sorting issue check the rows after sorting, because I got the proper answer. check below.

Happy to help.