1 of 1 people found this helpful
I believe I have figured this out, but I would like someone to review because I have to believe there is a simpler way than going through what I did.
What I did involved multiple calculate fields, along with a little error correction, but I do hope everyone can follow along. For the example, we will assume that Excel has provided a time of 40:13:10 in the [h]:mm:ss format.
Since Tableau will display total seconds for any period under 24 hours, I needed to get the difference in days and translate that into total seconds; 86400 seconds = 1 Day.
When I converted Total Time to the data type 'Date & time', Tableau displayed it in the Data Source as 1/1/1900 4:13:10 PM. I had to find the date difference in days from the Tableau's default date of 12/30/1899 12:00:00 AM. I created a calculated field for a reference date, DATE("12/30/1899"). From here I used the below formula for another calculated field called [Days Difference]:
DATEDIFF('day',[Reference Date - 12/30/1899],[Total Time])
This provided a result of 2 days difference; this is incorrect as 40 Hours 13 Min 10 Sec is not 2 days, but according to the data type conversion providing the date of 1/1/1900 4:13:10 PM, it was. To correct for this, I had to use this formula to find the correct days difference:
IF DATEDIFF('day',[Reference Date - 12/30/1899],[Total Time])>=1
THEN (DATEDIFF('day',[Reference Date - 12/30/1899],[Total Time])-1)
ELSE DATEDIFF('day',[Reference Date - 12/30/1899],[Total Time])
This provided the result of 1 day; which is correct.
I then used another calculated field to provide the total seconds for the Days Difference, using the below formula called [Days in Total Seconds]:
After this, I used a final calculated field to add both [Days in Total Seconds] and portion of time that was less than 1 day, called [Total Seconds First Contact (LESS24 hours)]; this calculated field was simple addition called [Total Seconds] and was the below formula:
[Days in Total Seconds] + [Total Seconds First Contact (LESS24 hours)]
This provided the result of 144790 seconds, which is the correct amount of seconds for 40 Hours 13 Minutes 10 Seconds; this is verified through (40*3600)+(13*60)+10.
Somebody please tell me there is an easier way to do this; also, if possible, can someone explain why 40:13:10 in the Excel format of [h]:mm:ss when the data type is changed from 'String' to 'Date & Time' shows 1/1/1900 4:13:10 PM when I believe the default date for Tableau is 12/30/1899 12:00:00 AM?
I was typing my answer (below) just before this post disappeared but here it is anyway.
Looks like you are on the right track.
Time only type is not really supported by Tableau but I can offer two workarounds:
- Treat column as string, split on ":", get individual fileds for hh/mm/ss and do the math on those
- Use Year part of the date: if it's not 12/30/1899 then you are dealing with overflow
DATEPART('hour',[Total Time]) * 3600 + DATEPART('minute',[Total Time])*60 + DATEPART('second',[Total Time]) + IIF(DATEPART('year',[Total Time]) >= 1900, DATEPART('day',[Total Time])*86400, 0)
See attached workbook for both and hope you don't deal with negative time
p.s. Formula in #2 is not bulletproof as it won't work correctly for times over 31 day
time.twbx 7.9 KB
Here is a different approach when you have a time field as a string in the following format:
HH: MM: SS
Calculated filed for hours, minutes, and second:
Minutes=MID([Time Field],FIND([Time Field],":")+1,2)
Hours=REPLACE([Time Field],RIGHT([Time Field],6),"")
Now you can have the total duration in any desired format, for instance in order to get total time in hours:
Total Duration= ROUND(FLOAT([Hours])+ (FLOAT([Minutes)/60) + (FLOAT(Second)/3600),1)