In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How do I convert Hours in time to a number that can be summarized?

SAConsultant
5 - Atom

Hello,

 

I have a dataset of payroll information from Quickbooks containing hours worked by day and individual.  I need to be able to summarize hours worked by individual, but am having trouble finding the appropriate method to convert the hours into a numerical format that can be transformed with a summarize tool. Can someone help me determine how to convert the hours into a format that can be summarized?

 

Attached excel contains a cleansed subset of the base input. Columns A-C reflect the raw data, and column D reflects my attempt to the convert the hours from column C(which has a data type of "Time" in designer) to numbers (using a ToNumber function)

 

As you can see, anytime an individual has hours that are not whole (i.e. rows 3,4, and 6), the conversion isn't accurate. Column C currently reads as Hours:minutes:seconds.

2 REPLIES 2
markcurry
12 - Quasar

Hi @SAConsultant   I think the following formula will do the trick for you, to give you the time in minutes (ignoring any seconds)....

 

(ToNumber(Substring([Hours],0,2)) * 60) + ToNumber(Substring([Hours],3,2))

 

atcodedog05
22 - Nova
22 - Nova

Hi @SAConsultant 

 

Is this what you are looking for

Output:

atcodedog05_0-1604341662065.png

Workflow:

atcodedog05_1-1604341674208.png

 

Hope this helps 🙂


If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

Labels
Top Solution Authors