How to handle Time in Alteryx?

Question

Hi,

From a data dump, I have the time formatted as HHH:MM;SS eg. 987:34:07

So that is 987 hours, 34 minutes and 7 seconds.

Running this in Excel I can easily translate this into days and hours, or even find max, min and average time.

Do I really have to transform this into three columns to do the same in Alteryx, or is there a way to convert this string into Time format ?

Br
/Thomas

tonypreece · Accepted Answer

Excel is doing a calculation based on the data 1 Jan 1900.

987 hours after 1/1/1900 00:00:00 is 10 Feb 1900.

To work out the days, it needs to figure out the difference between 10 Feb and 1 Jan, or...

floor(987/24) = 41 days and three hours.

In Alteryx:

Time field: 987:34:07 (type String)

Add a formula took with three formulas:
Create a field called Hours: tonumber(left([Time],3)) (type Int16)

Create a field called Days: floor([Hours]/24) (type Int16)

Change the field called Hours: [Hours]-([Days]*24)

This will leave you with
Days: 41
Hours: 3