Summing up Duration of course

[neilmc]

Hello,

    I'm getting a bit lost in the various solutions offered for summing times (sorry this is likely my skill levels).  I have a list of course duration times - some are greater than 24hours.  I am looking to calculate a total of all the durations.  If I convert it to a time in query editor (to change to a duration) I get errors as the times are greater than 24 hours.

Any help gratefully recieved Thank you....

SCO Total TIme

30:11:06.38

01:06:34.81

21:17:09.68

01:19:33.94

00:05:29.68

00:35:09.38

01:09:28.76

02:44:01.92

00:13:48.73

01:12:37.65

00:54:12.53

01:27:43.13

00:14:23.16

00:38:25.97

02:39:37.12

01:41:26.85

01:17:46.09

01:02:36.07

02:42:31.59

01:06:39.18

01:22:53.15

00:46:39.36

01:07:59.13

01:37:34.23

01:16:18.60

01:30:09.57

01:29:55.04

01:14:21.89

00:25:07.18

00:07:28.90

00:00:32.50

01:11:14.72

01:11:49.74

00:59:38.21

01:05:32.34

01:27:41.07

00:13:00.07

00:16:13.13

01:21:23.47

00:39:42.63

00:16:09.86

01:04:43.60

02:17:52.83

00:59:39.91

00:06:25.66

00:47:54.71

01:21:16.18

01:18:12.52

00:32:06.47

00:24:05.53

02:41:36.52

00:00:06.19

01:20:05.40

00:00:28.36

02:11:45.89

00:06:31.86

02:10:19.93

01:20:41.26

01:02:28.71

01:03:41.18

02:00:35.52

01:11:31.24

00:56:34.24

01:24:18.60

01:12:16.10

02:39:40.23

01:51:53.07

01:04:55.19

00:10:14.27

00:10:50.01

00:44:48.36

02:28:10.94

01:37:02.07

01:01:27.67

01:01:23.02

02:15:41.75

00:08:56.25

03:19:14.09

00:59:54.30

01:25:18.56

01:23:51.90

00:15:44.01

02:46:17.26

01:21:48.75

00:23:44.32

00:12:53.56

01:03:45.45

01:00:44.61

00:35:37.02

03:00:03.05

00:27:59.10

02:01:28.50

00:00:18.32

01:11:18.55

00:00:49.81

00:28:28.13

01:07:25.62

01:18:30.69

00:10:02.73

00:05:37.56

00:29:04.25

00:38:14.57

00:05:58.98

01:18:03.70

00:37:25.43

01:30:17.48

01:15:44.62

00:23:35.66

00:21:06.78

00:12:21.17

01:17:20.82

00:21:48.65

00:20:18.61

00:00:36.95

00:15:44.52

00:30:21.64

00:00:16.74

00:31:08.32

01:06:52.92

00:19:22.99

03:23:16.44

00:20:44.80

00:35:45.67

00:49:58.97

00:11:07.60

00:05:55.36

01:21:17.97

01:44:57.01

00:13:52.79

00:01:05.25

01:03:03.85

01:16:39.49

00:30:54.15

01:14:59.21

00:00:51.53

00:24:20.05

00:24:00.14

00:26:12.59

00:09:41.38

01:19:41.08

00:39:47.26

00:34:21.89

03:53:57.54

00:08:43.95

00:11:13.70

00:00:06.06

00:43:37.69

00:11:50.55

00:26:48.25

01:54:39.05

00:24:43.41

00:11:46.26

00:11:08.14

00:22:01.15

00:20:23.22

00:06:10.59

00:09:37.24

00:40:40.84

00:05:56.49

00:17:22.11

00:20:12.16

00:31:43.76

00:12:27.80

00:22:58.51

00:18:11.52

00:06:58.48

00:21:42.98

00:50:16.20

00:11:41.02

00:00:04.77

00:28:08.49

00:43:35.44

00:00:37.78

00:18:32.71

00:57:11.53

04:57:50.09

01:02:59.12

00:49:29.18

03:41:21.35

00:00:14.72

[mlsx4 · Accepted solution]

I have found an approach:

- First, in Power Query you need to separate the time by character : (You will get three columns: hours and minutes will be integer and seconds will be decimal).

-Then, create a column Time: 

Time = 'Table'[SCO Total hours] + ('Table'[SCO Total minutes]/60)+('Table'[SCO Total seconds]/3600)

Finally, two measures:

Total time= SUM('Table'[Time])

 And if you want a format like (HH:MM:SS) use this:

Total duration in HH:MM:SS = 

VAR DurationD = ROUND([Suma de Time],2) * 3600
VAR Hours = INT(DIVIDE(DurationD,3600,0))
VAR Minutes = INT(DIVIDE(MOD( DurationD - ( Hours * 3600 ),3600),60,0))
VAR Seconds = ROUNDUP(MOD ( MOD( DurationD - ( Hours * 3600 ),3600 ), 60 ),0)

//Get values formatted to two decimals

VAR H = IF (LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ))
VAR M = IF (LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ), CONCATENATE ( "", Minutes ))
VAR S = IF (LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ), CONCATENATE ( "", Seconds ))

RETURN 

//Check for empty values or errors
IFERROR(IF (DurationD>0, H & ":" & M & ":" & S,"0:00"),"Error")

Hope it helps 🙂

[neilmc]

Brilliant thank you!