Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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.
Hi @dan205 ,
Can you share this data in text, so that I can work on that and say you the result?
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
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.
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
Hi @dan205 ,
I think there is sorting issue check the rows after sorting, because I got the proper answer. check below.
Happy to help.