9 Replies Latest reply on May 27, 2016 4:21 AM by Andrew Watson

# Total Time

Hi guys,

I have an excel document with time's per agent of how long they spend on the phone.

So the excel document looks like this..

Agent Name

Time Spent on phone
Agent 107:49:00
Agent 207:30:00
Agent 306:59:00

I now need to add up the total time that they are spending on the phone in Tableau.

Does anyone know how to do this? Grand total isn't working as the time is not a measure.

If anyone can help that would be great:)

Kind Regards,

Oli

• ###### 1. Re: Total Time

You can split the time components out either using the Text functions, LEFT, RIGHT, etc or Split the data on import Split a Field into Multiple Fields

Once you have the data in hours, mins and seconds you are able to sum these values separately. I suggest converting the numbers to seconds so you can add the hours (in seconds), mins (in seconds) and seconds to get the total time in seconds. Once you have total time in seconds you can then convert this back to hours, mins and secs. Converting Seconds to HH:MM:SS or DD:HH:MM:SS | Tableau Software

A bit convoluted but this is one way to do it.

• ###### 2. Re: Total Time

Hi Andrew,

Doesn't seem to let me use RIGHT() against a datetime and also not letting me split it out either. What should I do?

Thanks,

Oli

• ###### 3. Re: Total Time

Convert it to string first. The STR function.

• ###### 4. Re: Total Time

Hi Andrew,

This is alot more complicated than I first envisioned, I thought there would be a simple button to Total up time as I would assume this is a common use.  Oh well, I'll have to live!

How do I convert hours to seconds?

Is there a simple button? I'm not very good at Calculated fields.

Kind Regards,

Oli

• ###### 5. Re: Total Time

To convert hours to seconds multiply by 60 and again by 60 (or multiply once by 3600, your choice).

The formula within your calculated field would be: [HoursField] * 60 * 60

• ###### 6. Re: Total Time

Hi Andrew,

Okay I tried that but it didn't quite give me the outcome I was looking for.

Could you possibly tell me why this isn't working ? Sorry for the bad image quality, attached.

Kind Regards,

Oli

• ###### 7. Re: Total Time

Please upload a twbx file otherwise it's a shot in the dark as to what is happening.

• ###### 8. Re: Total Time

Hi Andrew,

Attached, I want the grand totals to add up the total time spent.

• ###### 9. Re: Total Time

I'm on a different version of Tableau to you so can't upload the twbx as you won't be able to open it. However I'll show you what to do. Based on your attached dataset, create the following fields:

Hours: INT(LEFT([Staffed Time],1))

Mins: int(RIGHT(LEFT([Staffed Time],4),2))

Secs: INT(RIGHT([Staffed Time],2))

HoursInSecs: [Hours]*3600

MinsInSecs: [Mins] * 60

Total Secs: SUM([HoursInSecs]) + SUM([MinsInSecs]) + SUM([Secs])

TotalTime:

STR(INT([TotalSecs]/3600))

+ ":" +

IF INT([TotalSecs]%3600/60)

< 10 THEN "0" ELSE "" END + STR(INT([TotalSecs]%3600/60))

+ ":" +

IF INT([TotalSecs] %3600 %60)

< 10 THEN "0" ELSE "" END + STR(INT([TotalSecs])%3600 %60)

That will give you the following: