Forum Discussion

Eittep's avatar
Eittep
Copper Contributor
Feb 14, 2022

Cannot SUM times in HH:MM:SS format

Hi all, first time posting and hoping someone could please tell me what I'm missing here.

 

I'm working with some data exported from a web-based call database. The excel output gives a column of the times that each agent has spent on a call on a certain subject. I have formatted the data as a table. 

 

All of the times are given in the format HH:MM:SS. I can use the HOURS, MINUTE & SECOND functions to grab the corresponding part of the times in the table column ([Call Time]), however, I cannot SUM the times to get a total in HH:MM:SS format like I can with times in H:MM:SS.

 

What I think I need to do is convert HH:MM:SS to H:MM:SS. What I have done is created three other columns, for the hours, minutes and seconds in the [Call data] column named [Hours], [Minutes] & [Seconds]. I can SUM these columns to get a total time on calls using this formula:

 

=((SUM(Table1[Hours])*3600)+(SUM(Table1[Minutes])*60)+SUM(Table1[Seconds]))/86400

 

I format the result as H:MM:SS and I get a different time than what is shown in the web portal. 

 

I don't know if my working is wrong or if the data in the web portal is wrong. Any help would be appreciated, thanks.

1 Reply

Resources