10 Replies Latest reply on Jan 13, 2014 11:28 AM by Derek Arsenault

# Using Time (Hours, Minutes, Seconds) as a Measure

I have a lot of data points that include hours, minutes, and seconds. These are formatted as such in Excel ([h]:mm:ss). When in Tableau, I just want to do easy sums of this data, but, since it is formatted as a date/time, it won't let me move it to the measure column while keeping the time format. Any suggestions?

• ###### 1. Re: Using Time (Hours, Minutes, Seconds) as a Measure

Hello Nick,

Basically Tableau has built in time intelligence that works in your favor - i.e. it creates a time hierarchy automatically for you for every date/time field in your database. Thus this hierarchy is treated as a dimension, and you cannot perform simple math functions on it, as it cannot be converted to a measure. Actually if you are doing simple math with dates, then you should handle carefully periods having different length - e.g. February has 28 or 29 days, etc. Therefore for date/time fields you have the DATEADD and DATEDIFF functions at disposal. These basically perform addition and subtraction but with dates. Have a lok at these, and if they do not suit your requirements, drop me a line here providing some sample of what you are trying to achieve.

Hope this helps,

• ###### 2. Re: Using Time (Hours, Minutes, Seconds) as a Measure

Vladi - Thanks for your reply. I was hoping to avoid using a calculation, but understand the reason why Tableau won't transform times to a measure. So, I'm not that well versed in the calculation syntax beyond basic stuff. As I understand it, DATEADD basically allows you to add a fixed value to a part of the date, unlike DATEDIFF which allows you to just take the difference between two time values. I'm struggling to find out how to simply develop a calculation that will sum the times I have in a table. I have a bunch of values, I'll call them "race_times," that are in hh:mm:ss format. Any thoughts on what the syntax would look like on that calculation?

Nick

• ###### 3. Re: Using Time (Hours, Minutes, Seconds) as a Measure

Hi,

Attached is one way to do this. The DATEPART() function is used in the # of Seconds calc to extract the hours, minutes, and seconds to turn everything into seconds with the following formula:

DATEPART('hour',[Time DateTime]) * 3600 + DATEPART('minute',[Time DateTime])*60 + DATEPART('second',[Time DateTime])

Now there's a measure that can be used. To go from # of Seconds back to HH:MM:SS, here's a hh:mm:ss string calc with the following formula:

STR(INT(SUM( [# of Seconds] )/3600)) + ":" +

// convert minutes back to a string
IF LEN(STR(INT((SUM([# of Seconds]) % 3600 ) / 60))) = 1 THEN
"0" + STR(INT((SUM([# of Seconds]) % 3600 ) / 60))
ELSE
STR(INT((SUM([# of Seconds]) % 3600 ) / 60))
END

+ ":" +

//convert seconds back to a string

IF LEN(STR(INT(SUM([# of Seconds]) % 60))) = 1 THEN
"0" + STR(INT(SUM([# of Seconds]) % 60))
ELSE
STR(INT(SUM([# of Seconds]) % 60))
END

Cheers,

Jonathan

6 of 6 people found this helpful
• ###### 4. Re: Using Time (Hours, Minutes, Seconds) as a Measure

Thanks, Jonathan - worked perfectly. I appreciate it.

Nick

• ###### 5. Re: Using Time (Hours, Minutes, Seconds) as a Measure

Jonathan - do you know of a way to convert the hh:mm:ss text string to a measure so I can total it and sort it as such?

• ###### 6. Re: Using Time (Hours, Minutes, Seconds) as a Measure

Another version of the first calculation is:

(FLOAT([Time DateTime])+2)*24*60*60

This will be much faster than using DATEPART but will only work for some databases.

The FLOAT converts the Date & Time field to a number, using the rules of the database. The +2 is because times are stored as dates on Excel's zero day, but Jet's zero day is two days later. The *24*60*60 is because you get a result in days and it has to be converted to seconds.

Dan

• ###### 7. Re: Using Time (Hours, Minutes, Seconds) as a Measure

Thanks Dan. To clarify my question - I have 10 columns containing seconds values (eg 900 seconds) that users want displayed in a hh:mm:ss format. This means converting to a string and adding the string column as a row, which removes the ability to sort effectively. In addition to the 10 seconds column we have some additive measures that also need to display on the same row.

What I've done is create a parameter, a sheet for each converted hh:mm:ss column and to the far right, the minute value of the sheet so the user to sort on that column.

I'm hoping there is a better way. • ###### 8. Re: Using Time (Hours, Minutes, Seconds) as a Measure

You don't need to use string functions - you can convert the seconds to fractions of a day: [s]/(60*60*24), change this to a datetime, then format the output as hh:mm:ss as a custom format for that calculated field.

• ###### 9. Re: Using Time (Hours, Minutes, Seconds) as a Measure

It's a good solution but what if you only have a time interval such as 08:22:01 (hh:mm:ss) and no DateTime stamp to reference to like in your example? I need a way to convert the hh:mm:ss format to a calculated FLOAT value (in minutes) such as in this example:

08:22:01 should calculate to 502.02 minutes.

• ###### 10. Re: Using Time (Hours, Minutes, Seconds) as a Measure

I think I figured out a way...a little convoluted but it seems to work. hh:mm:ss field needs to be formated as a string for these calcs to work.

Create 3 different calculated values:

calc_Seconds

RIGHT([THE_hh:mm:ss_FIELD_HERE],2)

calc_Minutes

MID([THE_hh:mm:ss_FIELD_HERE],FIND([THE_hh:mm:ss_FIELD_HERE],":")+1,2)

calc_Hours

REPLACE([THE_hh:mm:ss_FIELD_HERE],RIGHT([THE_hh:mm:ss_FIELD_HERE],6),"")

Now we can add them all up into an accurate representation of total duration, in this example the number of hours EDIT: minutes rounded to one decimal point:

calc_TOTAL_DURATION

ROUND((FLOAT([calc_OUTAGE_HOURS])*60) + FLOAT([calc_OUTAGE_MINUTES]) + (FLOAT([calc_OUTAGE_SECONDS])/60),1)

EDIT: So in my example above with a string of 08:22:01 the calculated values would be:

ROUND(8*60)+(22)+(1/60),1)

= 480 + 22 + 0.17

= 502.17 minutes

3 of 3 people found this helpful